Open ILoveBuns opened 4 years ago
CREATE TABLE tb1 ( ability_id int(11) NOT NULL, domain_id int(11) NOT NULL, created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, last_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (ability_id,domain_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
tb1
ability_id
domain_id
created
last_modified
CREATE TABLE tb2 ( symbol int(10) unsigned NOT NULL, domain_id int(10) unsigned NOT NULL, ability_id int(10) unsigned NOT NULL, created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, last_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (symbol,domain_id,ability_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
tb2
symbol
select t1.ABILITY_ID from tb2 t2 join tb1 t1 on t2.ABILITY_ID = t1.ABILITY_ID and t1.DOMAIN_ID = t2.DOMAIN_ID
This suggestion is wrong about collation,because collation only affects char、varchar、text type。
CREATE TABLE
tb1
(ability_id
int(11) NOT NULL,domain_id
int(11) NOT NULL,created
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,last_modified
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (ability_id
,domain_id
) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;CREATE TABLE
tb2
(symbol
int(10) unsigned NOT NULL,domain_id
int(10) unsigned NOT NULL,ability_id
int(10) unsigned NOT NULL,created
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,last_modified
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (symbol
,domain_id
,ability_id
) ) ENGINE=InnoDB DEFAULT CHARSET=utf8select t1.ABILITY_ID from tb2 t2 join tb1 t1 on t2.ABILITY_ID = t1.ABILITY_ID and t1.DOMAIN_ID = t2.DOMAIN_ID
This suggestion is wrong about collation,because collation only affects char、varchar、text type。