| 1 |
DROP TABLE IF EXISTS t1, t2; |
| 2 |
SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; |
| 3 |
CREATE TABLE t1 ( |
| 4 |
id mediumint unsigned NOT NULL auto_increment, |
| 5 |
tag char(6) NOT NULL default '', |
| 6 |
value text NOT NULL default '', |
| 7 |
PRIMARY KEY (id), |
| 8 |
KEY kt(tag), |
| 9 |
KEY kv(value(15)), |
| 10 |
FULLTEXT KEY kvf(value) |
| 11 |
) ENGINE = InnoDB; |
| 12 |
Warnings: |
| 13 |
Warning 1101 BLOB, TEXT, GEOMETRY or JSON column 'value' can't have a default value |
| 14 |
SET sql_mode = default; |
| 15 |
CREATE TABLE t2 ( |
| 16 |
id_t2 mediumint unsigned NOT NULL default '0', |
| 17 |
id_t1 mediumint unsigned NOT NULL default '0', |
| 18 |
field_number tinyint unsigned NOT NULL default '0', |
| 19 |
PRIMARY KEY (id_t2,id_t1,field_number), |
| 20 |
KEY id_t1(id_t1) |
| 21 |
) ENGINE = InnoDB; |
| 22 |
INSERT INTO t1 (tag,value) VALUES ('foo123','bar111'); |
| 23 |
INSERT INTO t1 (tag,value) VALUES ('foo123','bar222'); |
| 24 |
INSERT INTO t1 (tag,value) VALUES ('bar345','baz333 ar'); |
| 25 |
INSERT INTO t2 VALUES (2231626,64280,0); |
| 26 |
INSERT INTO t2 VALUES (2231626,64281,0); |
| 27 |
INSERT INTO t2 VALUES (12346, 3, 1); |
| 28 |
SELECT * FROM t1; |
| 29 |
id tag value |
| 30 |
1 foo123 bar111 |
| 31 |
2 foo123 bar222 |
| 32 |
3 bar345 baz333 ar |
| 33 |
SELECT * FROM t2; |
| 34 |
id_t2 id_t1 field_number |
| 35 |
12346 3 1 |
| 36 |
2231626 64280 0 |
| 37 |
2231626 64281 0 |
| 38 |
SELECT DISTINCT t2.id_t2 FROM t2, t1 |
| 39 |
WHERE MATCH (t1.value) AGAINST ('baz333') AND t1.id = t2.id_t1; |
| 40 |
id_t2 |
| 41 |
12346 |
| 42 |
SELECT DISTINCT t2.id_t2 FROM t2, t1 |
| 43 |
WHERE MATCH (t1.value) AGAINST ('baz333' IN BOOLEAN MODE) |
| 44 |
AND t1.id = t2.id_t1; |
| 45 |
id_t2 |
| 46 |
12346 |
| 47 |
DROP TABLE t1, t2; |