mirror of
https://github.com/MariaDB/server.git
synced 2025-02-06 05:42:19 +01:00
920 lines
39 KiB
Text
920 lines
39 KiB
Text
set names utf8;
|
||
call mtr.add_suppression("\\[Warning\\] InnoDB: A new Doc ID must be supplied while updating FTS indexed columns.");
|
||
call mtr.add_suppression("\\[Warning\\] InnoDB: FTS Doc ID must be larger than [0-9]+ for table `test`.`t1`");
|
||
CREATE TABLE t1 (
|
||
id1 INT ,
|
||
a1 VARCHAR(200) ,
|
||
b1 TEXT ,
|
||
FULLTEXT KEY (a1,b1), PRIMARY KEY (a1, id1)
|
||
) CHARACTER SET = utf8 , ENGINE = InnoDB;
|
||
CREATE TABLE t2 (
|
||
id2 INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
|
||
a2 VARCHAR(200),
|
||
b2 TEXT ,
|
||
FOREIGN KEY (a2) REFERENCES t1(a1) ON UPDATE CASCADE,
|
||
FULLTEXT KEY (b2,a2)
|
||
) CHARACTER SET = utf8 ,ENGINE = InnoDB;
|
||
INSERT INTO t1 (id1,a1,b1) VALUES
|
||
(1,'MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') ,
|
||
(2,'How To Use MySQL Well','After you went through a ...'),
|
||
(3,'Optimizing MySQL','In this tutorial we will show ...');
|
||
INSERT INTO t1 (id1,a1,b1) VALUES
|
||
(4,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
|
||
(5,'MySQL vs. YourSQL','In the following database comparison ...'),
|
||
(6,'MySQL Security','When configured properly, MySQL ...');
|
||
INSERT INTO t2 (a2,b2) VALUES
|
||
('MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') ,
|
||
('How To Use MySQL Well','After you went through a ...'),
|
||
('Optimizing MySQL','In this tutorial we will show ...');
|
||
INSERT INTO t2 (a2,b2) VALUES
|
||
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
|
||
('MySQL vs. YourSQL','In the following database comparison ...'),
|
||
('MySQL Security','When configured properly, MySQL ...');
|
||
INSERT INTO t2 (a2,b2) VALUES
|
||
('MySQL Tricks','1. Never run mysqld as root. 2. ...');
|
||
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a2`) REFERENCES `t1` (`a1`) ON UPDATE CASCADE)
|
||
DELETE FROM t1;
|
||
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a2`) REFERENCES `t1` (`a1`) ON UPDATE CASCADE)
|
||
ANALYZE TABLE t1;
|
||
Table Op Msg_type Msg_text
|
||
test.t1 analyze status Engine-independent statistics collected
|
||
test.t1 analyze Warning Engine-independent statistics are not collected for column 'b1'
|
||
test.t1 analyze status OK
|
||
ANALYZE TABLE t2;
|
||
Table Op Msg_type Msg_text
|
||
test.t2 analyze status Engine-independent statistics collected
|
||
test.t2 analyze Warning Engine-independent statistics are not collected for column 'b2'
|
||
test.t2 analyze status OK
|
||
SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial') ORDER BY id1;
|
||
id1
|
||
1
|
||
3
|
||
SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial') ORDER BY id2;
|
||
id2
|
||
1
|
||
3
|
||
SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id1;
|
||
id1
|
||
1
|
||
2
|
||
3
|
||
4
|
||
5
|
||
6
|
||
SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id2;
|
||
id2
|
||
1
|
||
2
|
||
3
|
||
4
|
||
5
|
||
6
|
||
SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id1;
|
||
id1
|
||
1
|
||
2
|
||
3
|
||
4
|
||
5
|
||
6
|
||
SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id2;
|
||
id2
|
||
1
|
||
2
|
||
3
|
||
4
|
||
5
|
||
6
|
||
SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ;
|
||
id1
|
||
1
|
||
SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ;
|
||
id2
|
||
1
|
||
set global innodb_optimize_fulltext_only=1;
|
||
optimize table t1;
|
||
Table Op Msg_type Msg_text
|
||
test.t1 optimize status OK
|
||
set global innodb_optimize_fulltext_only=0;
|
||
UPDATE t1 SET a1 = "changing column - on update cascade" , b1 = "to check foreign constraint" WHERE
|
||
MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
|
||
SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
|
||
id1
|
||
SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
|
||
id2
|
||
3
|
||
6
|
||
SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('+update +cascade' IN BOOLEAN MODE) ORDER BY id1;
|
||
id1
|
||
1
|
||
2
|
||
3
|
||
4
|
||
5
|
||
6
|
||
SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('+update +cascade' IN BOOLEAN MODE) ORDER BY id2;
|
||
id2
|
||
1
|
||
2
|
||
3
|
||
4
|
||
5
|
||
6
|
||
SELECT id2 FROM t2 WHERE a2 LIKE '%UPDATE CASCADE%' ORDER BY id2;
|
||
id2
|
||
1
|
||
2
|
||
3
|
||
4
|
||
5
|
||
6
|
||
DROP TABLE t2 , t1;
|
||
create table t1 (s1 int, s2 varchar(200), primary key (s1,s2)) ENGINE = InnoDB;
|
||
create table t2 (s1 int, s2 varchar(200),
|
||
fulltext key(s2),
|
||
foreign key (s1,s2) references t1 (s1,s2) on update cascade) ENGINE = InnoDB;
|
||
insert into t1 values (1,'Sunshine'),(2,'Lollipops');
|
||
insert into t2 values (1,'Sunshine'),(2,'Lollipops');
|
||
update t1 set s2 = 'Rainbows' where s2 <> 'Sunshine';
|
||
commit;
|
||
select * from t2 where match(s2) against ('Lollipops');
|
||
s1 s2
|
||
DROP TABLE t2 , t1;
|
||
create table t1 (s1 int, s2 varchar(200), primary key (s1,s2)) ENGINE = InnoDB;
|
||
create table t2 (s1 int, s2 varchar(200),
|
||
fulltext key(s2),
|
||
foreign key (s1,s2) references t1 (s1,s2) on delete cascade) ENGINE = InnoDB;
|
||
insert into t1 values (1,'Sunshine'),(2,'Lollipops');
|
||
insert into t2 values (1,'Sunshine'),(2,'Lollipops');
|
||
delete from t1 where s2 <> 'Sunshine';
|
||
select * from t2 where match(s2) against ('Lollipops');
|
||
s1 s2
|
||
DROP TABLE t2 , t1;
|
||
create table t1 (s1 int, s2 varchar(200), primary key (s1,s2)) ENGINE = InnoDB;
|
||
create table t2 (s1 int, s2 varchar(200),
|
||
fulltext key(s2),
|
||
foreign key (s1,s2) references t1 (s1,s2) on delete set null) ENGINE = InnoDB;
|
||
insert into t1 values (1,'Sunshine'),(2,'Lollipops');
|
||
insert into t2 values (1,'Sunshine'),(2,'Lollipops');
|
||
delete from t1 where s2 <> 'Sunshine';
|
||
select * from t2 where match(s2) against ('Lollipops');
|
||
s1 s2
|
||
DROP TABLE t2 , t1;
|
||
create table t1 (s1 int, s2 varchar(200), primary key (s1,s2)) ENGINE = InnoDB;
|
||
create table t2 (s1 int, s2 varchar(200),
|
||
fulltext key(s2),
|
||
foreign key (s1,s2) references t1 (s1,s2) on update set null) ENGINE = InnoDB;
|
||
insert into t1 values (1,'Sunshine'),(2,'Lollipops');
|
||
insert into t2 values (1,'Sunshine'),(2,'Lollipops');
|
||
update t1 set s2 = 'Rainbows' where s2 <> 'Sunshine';
|
||
commit;
|
||
select * from t2 where match(s2) against ('Lollipops');
|
||
s1 s2
|
||
DROP TABLE t2 , t1;
|
||
create table t1 (s1 bigint unsigned not null, s2 varchar(200),
|
||
primary key (s1,s2)) ENGINE = InnoDB;
|
||
create table t2 (FTS_DOC_ID BIGINT UNSIGNED NOT NULL, s2 varchar(200),
|
||
foreign key (FTS_DOC_ID) references t1 (s1)
|
||
on update cascade) ENGINE = InnoDB;
|
||
create fulltext index idx on t2(s2);
|
||
show create table t2;
|
||
Table Create Table
|
||
t2 CREATE TABLE `t2` (
|
||
`FTS_DOC_ID` bigint(20) unsigned NOT NULL,
|
||
`s2` varchar(200) DEFAULT NULL,
|
||
KEY `FTS_DOC_ID` (`FTS_DOC_ID`),
|
||
FULLTEXT KEY `idx` (`s2`),
|
||
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`FTS_DOC_ID`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
||
insert into t1 values (1,'Sunshine'),(2,'Lollipops');
|
||
insert into t2 values (1,'Sunshine'),(2,'Lollipops');
|
||
update t1 set s1 = 3 where s1=1;
|
||
select * from t2 where match(s2) against ('sunshine');
|
||
FTS_DOC_ID s2
|
||
3 Sunshine
|
||
update t1 set s1 = 1 where s1=3;
|
||
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`FTS_DOC_ID`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE)
|
||
DROP TABLE t2 , t1;
|
||
CREATE TABLE t1 (
|
||
id1 INT ,
|
||
a1 VARCHAR(200) PRIMARY KEY,
|
||
b1 TEXT character set utf8 ,
|
||
FULLTEXT KEY (a1,b1)
|
||
) CHARACTER SET = utf8 ,ENGINE = InnoDB;
|
||
CREATE TABLE t2 (
|
||
id2 INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
|
||
a2 VARCHAR(200),
|
||
b2 TEXT character set utf8 ,
|
||
FOREIGN KEY (a2) REFERENCES t1(a1) ON DELETE CASCADE,
|
||
FULLTEXT KEY (b2,a2)
|
||
) CHARACTER SET = utf8 ,ENGINE = InnoDB;
|
||
INSERT INTO t1 (id1,a1,b1) VALUES
|
||
(1,'MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') ,
|
||
(2,'How To Use MySQL Well','After you went through a ...'),
|
||
(3,'Optimizing MySQL','In this tutorial we will show ...'),
|
||
(4,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
|
||
(5,'MySQL vs. YourSQL','In the following database comparison ...'),
|
||
(6,'MySQL Security','When configured properly, MySQL ...');
|
||
INSERT INTO t2 (a2,b2) VALUES
|
||
('MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') ,
|
||
('How To Use MySQL Well','After you went through a ...'),
|
||
('Optimizing MySQL','In this tutorial we will show ...'),
|
||
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
|
||
('MySQL vs. YourSQL','In the following database comparison ...'),
|
||
('MySQL Security','When configured properly, MySQL ...');
|
||
DELETE FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
|
||
SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
|
||
id1 a1 b1
|
||
SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
|
||
id2 a2 b2
|
||
SELECT * FROM t1 WHERE a1 LIKE '%tutorial%';
|
||
id1 a1 b1
|
||
SELECT * FROM t2 WHERE a2 LIKE '%tutorial%';
|
||
id2 a2 b2
|
||
DROP TABLE t2 , t1;
|
||
call mtr.add_suppression("\\[ERROR\\] InnoDB: FTS Doc ID must be larger than 3 for table `test`.`t2`");
|
||
CREATE TABLE t1 (
|
||
id1 INT ,
|
||
a1 VARCHAR(200) ,
|
||
b1 TEXT ,
|
||
FULLTEXT KEY (a1,b1), PRIMARY KEY(a1, id1)
|
||
) CHARACTER SET = utf8 , ENGINE = InnoDB;
|
||
CREATE TABLE t2 (
|
||
id2 INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
|
||
a2 VARCHAR(200),
|
||
b2 TEXT ,
|
||
FOREIGN KEY (a2) REFERENCES t1(a1) ON UPDATE CASCADE,
|
||
FULLTEXT KEY (b2,a2)
|
||
) CHARACTER SET = utf8 ,ENGINE = InnoDB;
|
||
INSERT INTO t1 (id1,a1,b1) VALUES
|
||
(1,'MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') ,
|
||
(2,'How To Use MySQL Well','After you went through a ...'),
|
||
(3,'Optimizing MySQL','In this tutorial we will show ...');
|
||
INSERT INTO t2 (a2,b2) VALUES
|
||
('MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') ,
|
||
('How To Use MySQL Well','After you went through a ...'),
|
||
('Optimizing MySQL','In this tutorial we will show ...');
|
||
START TRANSACTION;
|
||
INSERT INTO t1 (id1,a1,b1) VALUES
|
||
(4,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
|
||
(5,'MySQL vs. YourSQL','In the following database comparison ...'),
|
||
(6,'MySQL Security','When configured properly, MySQL ...');
|
||
INSERT INTO t2 (a2,b2) VALUES
|
||
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
|
||
('MySQL vs. YourSQL','In the following database comparison ...'),
|
||
('MySQL Security','When configured properly, MySQL ...');
|
||
INSERT INTO t2 (a2,b2) VALUES
|
||
('MySQL Tricks','1. Never run mysqld as root. 2. ...');
|
||
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a2`) REFERENCES `t1` (`a1`) ON UPDATE CASCADE)
|
||
DELETE FROM t1;
|
||
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a2`) REFERENCES `t1` (`a1`) ON UPDATE CASCADE)
|
||
SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial') ORDER BY id1;
|
||
id1 a1 b1
|
||
1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
|
||
3 Optimizing MySQL In this tutorial we will show ...
|
||
SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial') ORDER BY id2;
|
||
id2 a2 b2
|
||
1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
|
||
3 Optimizing MySQL In this tutorial we will show ...
|
||
SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id1;
|
||
id1 a1 b1
|
||
1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
|
||
2 How To Use MySQL Well After you went through a ...
|
||
3 Optimizing MySQL In this tutorial we will show ...
|
||
SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id2;
|
||
id2 a2 b2
|
||
1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
|
||
2 How To Use MySQL Well After you went through a ...
|
||
3 Optimizing MySQL In this tutorial we will show ...
|
||
SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id1;
|
||
id1 a1 b1
|
||
1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
|
||
2 How To Use MySQL Well After you went through a ...
|
||
3 Optimizing MySQL In this tutorial we will show ...
|
||
SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id2;
|
||
id2 a2 b2
|
||
1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
|
||
2 How To Use MySQL Well After you went through a ...
|
||
3 Optimizing MySQL In this tutorial we will show ...
|
||
SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ;
|
||
id1 a1 b1
|
||
1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
|
||
SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ;
|
||
id2 a2 b2
|
||
1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
|
||
SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('root') ;
|
||
id1 a1 b1
|
||
SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('root') ;
|
||
id2 a2 b2
|
||
SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('mysqld (+root)' IN BOOLEAN MODE) ;
|
||
id1 a1 b1
|
||
SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('mysqld (-root)' IN BOOLEAN MODE) ;
|
||
id2 a2 b2
|
||
SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('root' WITH QUERY EXPANSION) ;
|
||
id1 a1 b1
|
||
SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('root' WITH QUERY EXPANSION) ;
|
||
id2 a2 b2
|
||
SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('"database comparison"@02' IN BOOLEAN MODE) ;
|
||
id1 a1 b1
|
||
SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('"database comparison"@02' IN BOOLEAN MODE) ;
|
||
id2 a2 b2
|
||
SELECT * FROM t1 ORDER BY id1;
|
||
id1 a1 b1
|
||
1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
|
||
2 How To Use MySQL Well After you went through a ...
|
||
3 Optimizing MySQL In this tutorial we will show ...
|
||
4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
|
||
5 MySQL vs. YourSQL In the following database comparison ...
|
||
6 MySQL Security When configured properly, MySQL ...
|
||
SELECT * FROM t2 ORDER BY id2;
|
||
id2 a2 b2
|
||
1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
|
||
2 How To Use MySQL Well After you went through a ...
|
||
3 Optimizing MySQL In this tutorial we will show ...
|
||
4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
|
||
5 MySQL vs. YourSQL In the following database comparison ...
|
||
6 MySQL Security When configured properly, MySQL ...
|
||
COMMIT;
|
||
START TRANSACTION;
|
||
UPDATE t1 SET a1 = "changing column - on UPDATE cascade" , b1 = "to check foreign constraint" WHERE
|
||
MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
|
||
COMMIT;
|
||
SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
|
||
id1 a1 b1
|
||
SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
|
||
id2 a2 b2
|
||
3 changing column - on UPDATE cascade In this tutorial we will show ...
|
||
6 changing column - on UPDATE cascade When configured properly, MySQL ...
|
||
SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('+UPDATE +cascade' IN BOOLEAN MODE) ORDER BY id1;
|
||
id1 a1 b1
|
||
1 changing column - on UPDATE cascade to check foreign constraint
|
||
2 changing column - on UPDATE cascade to check foreign constraint
|
||
3 changing column - on UPDATE cascade to check foreign constraint
|
||
4 changing column - on UPDATE cascade to check foreign constraint
|
||
5 changing column - on UPDATE cascade to check foreign constraint
|
||
6 changing column - on UPDATE cascade to check foreign constraint
|
||
SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('+UPDATE +cascade' IN BOOLEAN MODE) ORDER BY id2;
|
||
id2 a2 b2
|
||
1 changing column - on UPDATE cascade DBMS stands for DataBase VÐƷWİ...
|
||
2 changing column - on UPDATE cascade After you went through a ...
|
||
3 changing column - on UPDATE cascade In this tutorial we will show ...
|
||
4 changing column - on UPDATE cascade 1. Never run mysqld as root. 2. ...
|
||
5 changing column - on UPDATE cascade In the following database comparison ...
|
||
6 changing column - on UPDATE cascade When configured properly, MySQL ...
|
||
SELECT * FROM t2 WHERE a2 LIKE '%UPDATE CASCADE%' ORDER BY id2;
|
||
id2 a2 b2
|
||
1 changing column - on UPDATE cascade DBMS stands for DataBase VÐƷWİ...
|
||
2 changing column - on UPDATE cascade After you went through a ...
|
||
3 changing column - on UPDATE cascade In this tutorial we will show ...
|
||
4 changing column - on UPDATE cascade 1. Never run mysqld as root. 2. ...
|
||
5 changing column - on UPDATE cascade In the following database comparison ...
|
||
6 changing column - on UPDATE cascade When configured properly, MySQL ...
|
||
DROP TABLE t2 , t1;
|
||
CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
|
||
CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
|
||
FULLTEXT KEY(s2),
|
||
FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on UPDATE cascade) ENGINE = InnoDB;
|
||
START TRANSACTION;
|
||
INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
|
||
INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
|
||
UPDATE t1 set s2 = 'Rainbows' WHERE s2 <> 'Sunshine';
|
||
COMMIT;
|
||
SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
|
||
s1 s2
|
||
DROP TABLE t2 , t1;
|
||
CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
|
||
CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
|
||
FULLTEXT KEY(s2),
|
||
FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on DELETE cascade) ENGINE = InnoDB;
|
||
START TRANSACTION;
|
||
INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
|
||
INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
|
||
DELETE FROM t1 WHERE s2 <> 'Sunshine';
|
||
COMMIT;
|
||
SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
|
||
s1 s2
|
||
DROP TABLE t2 , t1;
|
||
CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
|
||
CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
|
||
FULLTEXT KEY(s2),
|
||
FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on DELETE SET NULL) ENGINE = InnoDB;
|
||
START TRANSACTION;
|
||
INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
|
||
INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
|
||
DELETE FROM t1 WHERE s2 <> 'Sunshine';
|
||
COMMIT;
|
||
SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
|
||
s1 s2
|
||
DROP TABLE t2 , t1;
|
||
CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
|
||
CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
|
||
FULLTEXT KEY(s2),
|
||
FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on UPDATE SET NULL) ENGINE = InnoDB;
|
||
START TRANSACTION;
|
||
INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
|
||
INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
|
||
UPDATE t1 set s2 = 'Rainbows' WHERE s2 <> 'Sunshine';
|
||
COMMIT;
|
||
SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
|
||
s1 s2
|
||
DROP TABLE t2 , t1;
|
||
CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
|
||
CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
|
||
FULLTEXT KEY(s2),
|
||
FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on UPDATE cascade) ENGINE = InnoDB;
|
||
START TRANSACTION;
|
||
INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
|
||
INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
|
||
UPDATE t1 set s2 = 'Rainbows' WHERE s2 <> 'Sunshine';
|
||
ROLLBACK;
|
||
SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
|
||
s1 s2
|
||
DROP TABLE t2 , t1;
|
||
CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
|
||
CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
|
||
FULLTEXT KEY(s2),
|
||
FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on DELETE cascade) ENGINE = InnoDB;
|
||
START TRANSACTION;
|
||
INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
|
||
INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
|
||
DELETE FROM t1 WHERE s2 <> 'Sunshine';
|
||
ROLLBACK;
|
||
SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
|
||
s1 s2
|
||
DROP TABLE t2 , t1;
|
||
CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
|
||
CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
|
||
FULLTEXT KEY(s2),
|
||
FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on DELETE SET NULL) ENGINE = InnoDB;
|
||
START TRANSACTION;
|
||
INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
|
||
INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
|
||
DELETE FROM t1 WHERE s2 <> 'Sunshine';
|
||
ROLLBACK;
|
||
SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
|
||
s1 s2
|
||
DROP TABLE t2 , t1;
|
||
CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
|
||
CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
|
||
FULLTEXT KEY(s2),
|
||
FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on UPDATE SET NULL) ENGINE = InnoDB;
|
||
START TRANSACTION;
|
||
INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
|
||
INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
|
||
UPDATE t1 set s2 = 'Rainbows' WHERE s2 <> 'Sunshine';
|
||
ROLLBACK;
|
||
SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
|
||
s1 s2
|
||
DROP TABLE t2 , t1;
|
||
CREATE TABLE t1 (
|
||
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
|
||
a VARCHAR(200),
|
||
b TEXT
|
||
) CHARACTER SET = utf8, ROW_FORMAT=COMPRESSED, ENGINE = InnoDB;
|
||
INSERT INTO t1 (a,b) VALUES
|
||
('MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') ,
|
||
('How To Use MySQL Well','After you went through a ...'),
|
||
('Optimizing MySQL','In this tutorial we will show ...');
|
||
ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);
|
||
SHOW CREATE TABLE t1;
|
||
Table Create Table
|
||
t1 CREATE TABLE `t1` (
|
||
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
||
`a` varchar(200) DEFAULT NULL,
|
||
`b` text DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
FULLTEXT KEY `idx` (`a`,`b`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci ROW_FORMAT=COMPRESSED
|
||
SELECT count(*) FROM information_schema.innodb_sys_tables WHERE name LIKE "%FTS_%" AND space !=0;
|
||
count(*)
|
||
11
|
||
INSERT INTO t1 (a,b) VALUES
|
||
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
|
||
('MySQL vs. YourSQL','In the following database comparison ...'),
|
||
('MySQL Security','When configured properly, MySQL ...');
|
||
ANALYZE TABLE t1;
|
||
SELECT * FROM t1 WHERE MATCH (a,b)
|
||
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id;
|
||
id a b
|
||
1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
|
||
3 Optimizing MySQL In this tutorial we will show ...
|
||
select * from t1 where MATCH(a,b) AGAINST("+tutorial +VÐƷWİ" IN BOOLEAN MODE);
|
||
id a b
|
||
1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
|
||
select * from t1 where MATCH(a,b) AGAINST("+-VÐƷWİ" IN BOOLEAN MODE);
|
||
ERROR 42000: syntax error, unexpected '-'
|
||
select * from t1 where MATCH(a,b) AGAINST("+Mysql +(tricks never)" IN BOOLEAN MODE);
|
||
id a b
|
||
4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
|
||
select * from t1 where MATCH(a,b) AGAINST("+mysql -(tricks never)" IN BOOLEAN MODE) ORDER BY id;
|
||
id a b
|
||
1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
|
||
2 How To Use MySQL Well After you went through a ...
|
||
3 Optimizing MySQL In this tutorial we will show ...
|
||
5 MySQL vs. YourSQL In the following database comparison ...
|
||
6 MySQL Security When configured properly, MySQL ...
|
||
select *, MATCH(a,b) AGAINST("mysql stands" IN BOOLEAN MODE) as x from t1 ORDER BY id;
|
||
id a b x
|
||
1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 0.6055193543434143
|
||
2 How To Use MySQL Well After you went through a ... 0.000000001885928302414186
|
||
3 Optimizing MySQL In this tutorial we will show ... 0.000000001885928302414186
|
||
4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... 0.000000001885928302414186
|
||
5 MySQL vs. YourSQL In the following database comparison ... 0.000000001885928302414186
|
||
6 MySQL Security When configured properly, MySQL ... 0.000000003771856604828372
|
||
select * from t1 where MATCH a,b AGAINST ("+database* +VÐƷW*" IN BOOLEAN MODE);
|
||
id a b
|
||
1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
|
||
select * from t1 where MATCH a,b AGAINST ('"security mysql"' IN BOOLEAN MODE);
|
||
id a b
|
||
select * from t1 where MATCH(a,b) AGAINST ("VÐƷWİ" WITH QUERY EXPANSION) ORDER BY id;
|
||
id a b
|
||
1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
|
||
2 How To Use MySQL Well After you went through a ...
|
||
3 Optimizing MySQL In this tutorial we will show ...
|
||
4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
|
||
5 MySQL vs. YourSQL In the following database comparison ...
|
||
6 MySQL Security When configured properly, MySQL ...
|
||
ALTER TABLE t1 DROP INDEX idx;
|
||
CREATE FULLTEXT INDEX idx on t1 (a,b);
|
||
SELECT * FROM t1 WHERE MATCH (a,b)
|
||
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id;
|
||
id a b
|
||
1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
|
||
3 Optimizing MySQL In this tutorial we will show ...
|
||
select * from t1 where MATCH(a,b) AGAINST("+tutorial +VÐƷWİ" IN BOOLEAN MODE);
|
||
id a b
|
||
1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
|
||
select * from t1 where MATCH(a,b) AGAINST("+dbms" IN BOOLEAN MODE);
|
||
id a b
|
||
1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
|
||
select * from t1 where MATCH(a,b) AGAINST("+Mysql +(tricks never)" IN BOOLEAN MODE);
|
||
id a b
|
||
4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
|
||
select * from t1 where MATCH(a,b) AGAINST("+mysql -(tricks never)" IN BOOLEAN MODE) ORDER BY id;
|
||
id a b
|
||
1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
|
||
2 How To Use MySQL Well After you went through a ...
|
||
3 Optimizing MySQL In this tutorial we will show ...
|
||
5 MySQL vs. YourSQL In the following database comparison ...
|
||
6 MySQL Security When configured properly, MySQL ...
|
||
select *, MATCH(a,b) AGAINST("mysql VÐƷWİ" IN BOOLEAN MODE) as x from t1 ORDER BY id;
|
||
id a b x
|
||
1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 0.6055193543434143
|
||
2 How To Use MySQL Well After you went through a ... 0.000000001885928302414186
|
||
3 Optimizing MySQL In this tutorial we will show ... 0.000000001885928302414186
|
||
4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... 0.000000001885928302414186
|
||
5 MySQL vs. YourSQL In the following database comparison ... 0.000000001885928302414186
|
||
6 MySQL Security When configured properly, MySQL ... 0.000000003771856604828372
|
||
select * from t1 where MATCH a,b AGAINST ('"security mysql"' IN BOOLEAN MODE);
|
||
id a b
|
||
select * from t1 where MATCH(a,b) AGAINST ("VÐƷWİ" WITH QUERY EXPANSION) ORDER BY id;
|
||
id a b
|
||
1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
|
||
2 How To Use MySQL Well After you went through a ...
|
||
3 Optimizing MySQL In this tutorial we will show ...
|
||
4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
|
||
5 MySQL vs. YourSQL In the following database comparison ...
|
||
6 MySQL Security When configured properly, MySQL ...
|
||
INSERT INTO t1 (a,b) VALUES ('test query expansion','for database ...');
|
||
INSERT INTO t1 (a,b) VALUES
|
||
('test proximity search, test, proximity and phrase',
|
||
'search, with proximity innodb');
|
||
INSERT INTO t1 (a,b) VALUES
|
||
('test proximity fts search, test, proximity and phrase',
|
||
'search, with proximity innodb');
|
||
INSERT INTO t1 (a,b) VALUES
|
||
('test more proximity fts search, test, more proximity and phrase',
|
||
'search, with proximity innodb');
|
||
SELECT * FROM t1
|
||
WHERE MATCH (a,b)
|
||
AGAINST ('"proximity search"@2' IN BOOLEAN MODE);
|
||
id a b
|
||
8 test proximity search, test, proximity and phrase search, with proximity innodb
|
||
SELECT * FROM t1
|
||
WHERE MATCH (a,b)
|
||
AGAINST ('"proximity search"@1' IN BOOLEAN MODE);
|
||
id a b
|
||
SELECT * FROM t1
|
||
WHERE MATCH (a,b)
|
||
AGAINST ('"proximity search"@3' IN BOOLEAN MODE) ORDER BY id;
|
||
id a b
|
||
8 test proximity search, test, proximity and phrase search, with proximity innodb
|
||
9 test proximity fts search, test, proximity and phrase search, with proximity innodb
|
||
10 test more proximity fts search, test, more proximity and phrase search, with proximity innodb
|
||
SELECT * FROM t1
|
||
WHERE MATCH (a,b)
|
||
AGAINST ('"test proximity"@5' IN BOOLEAN MODE) ORDER BY id;
|
||
id a b
|
||
8 test proximity search, test, proximity and phrase search, with proximity innodb
|
||
9 test proximity fts search, test, proximity and phrase search, with proximity innodb
|
||
10 test more proximity fts search, test, more proximity and phrase search, with proximity innodb
|
||
SELECT * FROM t1
|
||
WHERE MATCH (a,b)
|
||
AGAINST ('"more test proximity"@2' IN BOOLEAN MODE);
|
||
id a b
|
||
SELECT * FROM t1
|
||
WHERE MATCH (a,b)
|
||
AGAINST ('"more test proximity"@3' IN BOOLEAN MODE);
|
||
id a b
|
||
10 test more proximity fts search, test, more proximity and phrase search, with proximity innodb
|
||
SELECT * FROM t1
|
||
WHERE MATCH (a,b)
|
||
AGAINST ('"more fts proximity"@03' IN BOOLEAN MODE);
|
||
id a b
|
||
10 test more proximity fts search, test, more proximity and phrase search, with proximity innodb
|
||
UPDATE t1 SET a = UPPER(a) , b = UPPER(b) ;
|
||
UPDATE t1 SET a = UPPER(a) , b = LOWER(b) ;
|
||
select * from t1 where MATCH(a,b) AGAINST("+tutorial +dbms" IN BOOLEAN MODE);
|
||
id a b
|
||
1 MYSQL TUTORIAL dbms stands for database vðʒwi...
|
||
select * from t1 where MATCH(a,b) AGAINST("+VÐƷWİ" IN BOOLEAN MODE);
|
||
id a b
|
||
1 MYSQL TUTORIAL dbms stands for database vðʒwi...
|
||
SELECT * FROM t1 WHERE MATCH (a,b)
|
||
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id;
|
||
id a b
|
||
1 MYSQL TUTORIAL dbms stands for database vðʒwi...
|
||
3 OPTIMIZING MYSQL in this tutorial we will show ...
|
||
DELETE FROM t1 WHERE MATCH (a,b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
|
||
DELETE FROM t1 WHERE MATCH (a,b) AGAINST ('"proximity search"@14' IN BOOLEAN MODE);
|
||
SELECT * FROM t1 WHERE MATCH (a,b)
|
||
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
|
||
id a b
|
||
SELECT * FROM t1 ORDER BY id;
|
||
id a b
|
||
2 HOW TO USE MYSQL WELL after you went through a ...
|
||
4 1001 MYSQL TRICKS 1. never run mysqld as root. 2. ...
|
||
5 MYSQL VS. YOURSQL in the following database comparison ...
|
||
6 MYSQL SECURITY when configured properly, mysql ...
|
||
7 TEST QUERY EXPANSION for database ...
|
||
DROP TABLE t1;
|
||
CREATE TABLE t1 (
|
||
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
|
||
a VARCHAR(200),
|
||
b TEXT
|
||
) CHARACTER SET = utf8, ENGINE=InnoDB;
|
||
INSERT INTO t1 (a,b) VALUES
|
||
('Я могу есть стекло', 'оно мне не вредит'),
|
||
('Мога да ям стъкло', 'то не ми вреди'),
|
||
('Μπορῶ νὰ φάω σπασμένα' ,'γυαλιὰ χωρὶς νὰ πάθω τίποτα'),
|
||
('Příliš žluťoučký kůň', 'úpěl ďábelské kódy'),
|
||
('Sævör grét', 'áðan því úlpan var ónýt'),
|
||
('うゐのおくやま','けふこえて'),
|
||
('いろはにほへど ちりぬる','あさきゆめみじ ゑひもせず');
|
||
INSERT INTO t1 (a,b) VALUES
|
||
('MySQL Tutorial','request docteam@oraclehelp.com ...') ,
|
||
('Trial version','query performace @1255 minute on 2.1Hz Memory 2GB...') ,
|
||
('when To Use MySQL Well','for free faq mail@xyz.com ...');
|
||
CREATE FULLTEXT INDEX idx on t1 (a,b);
|
||
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("вредит χωρὶς") ORDER BY id;
|
||
id a b
|
||
1 Я могу есть стекло оно мне не вредит
|
||
3 Μπορῶ νὰ φάω σπασμένα γυαλιὰ χωρὶς νὰ πάθω τίποτα
|
||
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("оно" WITH QUERY EXPANSION);
|
||
id a b
|
||
1 Я могу есть стекло оно мне не вредит
|
||
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("вред*" IN BOOLEAN MODE) ORDER BY id;
|
||
id a b
|
||
1 Я могу есть стекло оно мне не вредит
|
||
2 Мога да ям стъкло то не ми вреди
|
||
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+γυαλιὰ +tutorial" IN BOOLEAN MODE);
|
||
id a b
|
||
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+tutorial +(Мога τίποτα)" IN BOOLEAN MODE);
|
||
id a b
|
||
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず");
|
||
id a b
|
||
7 いろはにほへど ちりぬる あさきゆめみじ ゑひもせず
|
||
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("ちりぬる" WITH QUERY EXPANSION);
|
||
id a b
|
||
7 いろはにほへど ちりぬる あさきゆめみじ ゑひもせず
|
||
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("+あさきゆめみじ +ゑひもせず" IN BOOLEAN MODE);
|
||
id a b
|
||
7 いろはにほへど ちりぬる あさきゆめみじ ゑひもせず
|
||
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("うゐのおく*" IN BOOLEAN MODE);
|
||
id a b
|
||
6 うゐのおくやま けふこえて
|
||
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+Sævör +úlpan" IN BOOLEAN MODE);
|
||
id a b
|
||
5 Sævör grét áðan því úlpan var ónýt
|
||
SELECT * FROM t1
|
||
WHERE MATCH (a,b)
|
||
AGAINST ('"γυαλιὰ χωρὶς"@2' IN BOOLEAN MODE);
|
||
id a b
|
||
3 Μπορῶ νὰ φάω σπασμένα γυαλιὰ χωρὶς νὰ πάθω τίποτα
|
||
SELECT * FROM t1
|
||
WHERE MATCH (a,b)
|
||
AGAINST ('"query performace"@02' IN BOOLEAN MODE);
|
||
id a b
|
||
9 Trial version query performace @1255 minute on 2.1Hz Memory 2GB...
|
||
SELECT * FROM t1
|
||
WHERE MATCH (a,b)
|
||
AGAINST ('"πάθω τίποτα"@2' IN BOOLEAN MODE);
|
||
id a b
|
||
3 Μπορῶ νὰ φάω σπασμένα γυαλιὰ χωρὶς νὰ πάθω τίποτα
|
||
SELECT * FROM t1
|
||
WHERE MATCH (a,b)
|
||
AGAINST ('"あさきゆめみじ ゑひもせず"@1' IN BOOLEAN MODE);
|
||
id a b
|
||
SELECT * FROM t1
|
||
WHERE MATCH (a,b)
|
||
AGAINST ('"あさきゆめみじ ゑひもせず"@2' IN BOOLEAN MODE);
|
||
id a b
|
||
7 いろはにほへど ちりぬる あさきゆめみじ ゑひもせず
|
||
ALTER TABLE t1 DROP INDEX idx;
|
||
CREATE FULLTEXT INDEX idx on t1 (a,b);
|
||
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず");
|
||
id a b
|
||
7 いろはにほへど ちりぬる あさきゆめみじ ゑひもせず
|
||
UPDATE t1 SET a = "Pchnąć w tę łódź jeża" , b = "lub osiem skrzyń fig" WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず");
|
||
UPDATE t1 SET a = "В чащах юга жил-был цитрус? Да", b = "но фальшивый экземпляр! ёъ" WHERE MATCH(a,b) AGAINST ("вред*" IN BOOLEAN MODE);
|
||
DELETE FROM t1 WHERE MATCH(a,b) AGAINST("+Sævör +úlpan" IN BOOLEAN MODE);
|
||
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず");
|
||
id a b
|
||
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("łódź osiem");
|
||
id a b
|
||
7 Pchnąć w tę łódź jeża lub osiem skrzyń fig
|
||
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("вред*" IN BOOLEAN MODE);
|
||
id a b
|
||
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("фальшив*" IN BOOLEAN MODE) ORDER BY id;
|
||
id a b
|
||
1 В чащах юга жил-был цитрус? Да но фальшивый экземпляр! ёъ
|
||
2 В чащах юга жил-был цитрус? Да но фальшивый экземпляр! ёъ
|
||
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+Sævör +úlpan" IN BOOLEAN MODE);
|
||
id a b
|
||
SELECT * FROM t1
|
||
WHERE MATCH (a,b)
|
||
AGAINST ('"łódź jeża"@2' IN BOOLEAN MODE);
|
||
id a b
|
||
7 Pchnąć w tę łódź jeża lub osiem skrzyń fig
|
||
SELECT * FROM t1 ORDER BY id;
|
||
id a b
|
||
1 В чащах юга жил-был цитрус? Да но фальшивый экземпляр! ёъ
|
||
2 В чащах юга жил-был цитрус? Да но фальшивый экземпляр! ёъ
|
||
3 Μπορῶ νὰ φάω σπασμένα γυαλιὰ χωρὶς νὰ πάθω τίποτα
|
||
4 Příliš žluťoučký kůň úpěl ďábelské kódy
|
||
6 うゐのおくやま けふこえて
|
||
7 Pchnąć w tę łódź jeża lub osiem skrzyń fig
|
||
8 MySQL Tutorial request docteam@oraclehelp.com ...
|
||
9 Trial version query performace @1255 minute on 2.1Hz Memory 2GB...
|
||
10 when To Use MySQL Well for free faq mail@xyz.com ...
|
||
DROP TABLE t1;
|
||
CREATE TABLE t1(ID INT PRIMARY KEY,
|
||
no_fts_field VARCHAR(10),
|
||
fts_field VARCHAR(10),
|
||
FULLTEXT INDEX f(fts_field)) ENGINE=INNODB;
|
||
INSERT INTO t1 VALUES (1, 'AAA', 'BBB');
|
||
SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB");
|
||
ID no_fts_field fts_field
|
||
1 AAA BBB
|
||
UPDATE t1 SET fts_field='anychange' where id = 1;
|
||
SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange");
|
||
ID no_fts_field fts_field
|
||
1 AAA anychange
|
||
UPDATE t1 SET no_fts_field='anychange' where id = 1;
|
||
SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange");
|
||
ID no_fts_field fts_field
|
||
1 anychange anychange
|
||
UPDATE t1 SET no_fts_field='anychange', fts_field='other' where id = 1;
|
||
SELECT * FROM t1 WHERE MATCH(fts_field) against("other");
|
||
ID no_fts_field fts_field
|
||
1 anychange other
|
||
SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB");
|
||
ID no_fts_field fts_field
|
||
DROP INDEX f on t1;
|
||
UPDATE t1 SET fts_field='anychange' where id = 1;
|
||
UPDATE t1 SET no_fts_field='anychange' where id = 1;
|
||
UPDATE t1 SET no_fts_field='anychange', fts_field='other' where id = 1;
|
||
CREATE FULLTEXT INDEX f ON t1(FTS_FIELD);
|
||
SELECT * FROM t1 WHERE MATCH(fts_field) against("other");
|
||
ID no_fts_field fts_field
|
||
1 anychange other
|
||
DROP TABLE t1;
|
||
CREATE TABLE t1(`FTS_DOC_ID` serial,
|
||
no_fts_field VARCHAR(10),
|
||
fts_field VARCHAR(10),
|
||
FULLTEXT INDEX f(fts_field)) ENGINE=INNODB;
|
||
INSERT INTO t1 VALUES (1, 'AAA', 'BBB');
|
||
UPDATE t1 SET fts_field='anychange' where FTS_DOC_ID = 1;
|
||
ERROR HY000: Invalid InnoDB FTS Doc ID
|
||
UPDATE t1 SET fts_field='anychange', FTS_DOC_ID = 2 where FTS_DOC_ID = 1;
|
||
SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange");
|
||
FTS_DOC_ID no_fts_field fts_field
|
||
2 AAA anychange
|
||
SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB");
|
||
FTS_DOC_ID no_fts_field fts_field
|
||
UPDATE t1 SET no_fts_field='anychange' where FTS_DOC_ID = 2;
|
||
SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange");
|
||
FTS_DOC_ID no_fts_field fts_field
|
||
2 anychange anychange
|
||
UPDATE t1 SET no_fts_field='anychange', fts_field='other' where FTS_DOC_ID = 2;
|
||
ERROR HY000: Invalid InnoDB FTS Doc ID
|
||
SELECT * FROM t1 WHERE MATCH(fts_field) against("other");
|
||
FTS_DOC_ID no_fts_field fts_field
|
||
UPDATE t1 SET FTS_DOC_ID = 1 where FTS_DOC_ID = 2;
|
||
ERROR HY000: Invalid InnoDB FTS Doc ID
|
||
DROP INDEX f ON t1;
|
||
UPDATE t1 SET fts_field='newchange' where FTS_DOC_ID = 2;
|
||
UPDATE t1 SET no_fts_field='anychange' where FTS_DOC_ID = 2;
|
||
SELECT * FROM t1;
|
||
FTS_DOC_ID no_fts_field fts_field
|
||
2 anychange newchange
|
||
DROP TABLE t1;
|
||
CREATE TABLE t1(ID INT PRIMARY KEY,
|
||
no_fts_field VARCHAR(10),
|
||
fts_field VARCHAR(10),
|
||
FULLTEXT INDEX f(fts_field), index k(fts_field)) ENGINE=INNODB;
|
||
CREATE TABLE t2(ID INT PRIMARY KEY,
|
||
no_fts_field VARCHAR(10),
|
||
fts_field VARCHAR(10),
|
||
FULLTEXT INDEX f(fts_field),
|
||
INDEX k2(fts_field),
|
||
FOREIGN KEY(fts_field) REFERENCES
|
||
t1(fts_field) ON UPDATE CASCADE) ENGINE=INNODB;
|
||
INSERT INTO t1 VALUES (1, 'AAA', 'BBB');
|
||
INSERT INTO t2 VALUES (1, 'AAA', 'BBB');
|
||
update t1 set fts_field='newchange' where id =1;
|
||
SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB");
|
||
ID no_fts_field fts_field
|
||
SELECT * FROM t2 WHERE MATCH(fts_field) against("BBB");
|
||
ID no_fts_field fts_field
|
||
SELECT * FROM t1 WHERE MATCH(fts_field) against("newchange");
|
||
ID no_fts_field fts_field
|
||
1 AAA newchange
|
||
SELECT * FROM t2 WHERE MATCH(fts_field) against("newchange");
|
||
ID no_fts_field fts_field
|
||
1 AAA newchange
|
||
DROP TABLE t2;
|
||
DROP TABLE t1;
|
||
CREATE TABLE t1(id INT PRIMARY KEY,
|
||
fts_field VARCHAR(10),
|
||
FULLTEXT INDEX f(fts_field)) ENGINE=INNODB;
|
||
CREATE TABLE t2(id INT PRIMARY KEY,
|
||
fts_field VARCHAR(10),
|
||
FULLTEXT INDEX f(fts_field)) ENGINE=INNODB;
|
||
INSERT INTO t1 values (1,'100'),(2,'200'),(3,'300'),(4,'400'),(5,'500'),(6,'600'), (7,'700'),(8,'800'),(9,'900'),(10,'1000'),(11,'1100'),(12,'1200');
|
||
INSERT INTO t2 values (1,'100'),(2,'200'),(3,'300'),(4,'400'),(5,'500'),(6,'600'), (7,'700'),(8,'800');
|
||
UPDATE t1, t2 set t1.fts_field = CONCAT(t1.fts_field, 'foo');
|
||
UPDATE t1, t2 set t1.fts_field = CONCAT(t1.fts_field, 'foo') WHERE t1.fts_field = "100foo";
|
||
UPDATE t1, t2 set t1.fts_field = CONCAT(t1.fts_field, 'xoo'), t2.fts_field = CONCAT(t1.fts_field, 'xoo') where t1.fts_field=CONCAT(t2.fts_field, 'foo');
|
||
SELECT * FROM t1 WHERE MATCH(fts_field) against("100foofoo");
|
||
id fts_field
|
||
1 100foofoo
|
||
SELECT * FROM t1 WHERE MATCH(fts_field) against("100foo");
|
||
id fts_field
|
||
SELECT * FROM t1 WHERE MATCH(fts_field) against("100");
|
||
id fts_field
|
||
SELECT * FROM t2 WHERE MATCH(fts_field) against("400fooxoo");
|
||
id fts_field
|
||
4 400fooxoo
|
||
SELECT * FROM t2 WHERE MATCH(fts_field) against("100");
|
||
id fts_field
|
||
1 100
|
||
SELECT * FROM t2 WHERE MATCH(fts_field) against("200");
|
||
id fts_field
|
||
SELECT * FROM t2 WHERE MATCH(fts_field) against("400");
|
||
id fts_field
|
||
DROP TABLE t1;
|
||
DROP TABLE t2;
|
||
|
||
BUG#13701973/64274: MYSQL THREAD WAS SUSPENDED WHEN EXECUTE UPDATE QUERY
|
||
|
||
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
|
||
CREATE TABLE t1 (
|
||
t1_id INT(10) UNSIGNED NOT NULL,
|
||
t2_id INT(10) UNSIGNED DEFAULT NULL,
|
||
PRIMARY KEY (t1_id),
|
||
FOREIGN KEY (t2_id) REFERENCES t2 (t2_id)
|
||
ON DELETE CASCADE ON UPDATE CASCADE
|
||
) ENGINE=InnoDB;
|
||
CREATE TABLE t2 (
|
||
t1_id INT(10) UNSIGNED NOT NULL,
|
||
t2_id INT(10) UNSIGNED NOT NULL,
|
||
t3_id INT(10) UNSIGNED NOT NULL,
|
||
t4_id INT(10) UNSIGNED NOT NULL,
|
||
PRIMARY KEY (t2_id),
|
||
FOREIGN KEY (t1_id) REFERENCES t1 (t1_id),
|
||
FOREIGN KEY (t3_id) REFERENCES t3 (t3_id)
|
||
ON DELETE CASCADE ON UPDATE CASCADE,
|
||
FOREIGN KEY (t4_id) REFERENCES t4 (t4_id)
|
||
) ENGINE=InnoDB;
|
||
CREATE TABLE t3 (
|
||
t3_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
payload char(3),
|
||
PRIMARY KEY (t3_id)
|
||
) ENGINE=InnoDB;
|
||
INSERT INTO t3 VALUES (1, '100');
|
||
CREATE TABLE t4 (
|
||
t2_id INT(10) UNSIGNED DEFAULT NULL,
|
||
t4_id INT(10) UNSIGNED NOT NULL,
|
||
PRIMARY KEY (t4_id),
|
||
FOREIGN KEY (t2_id) REFERENCES t2 (t2_id)
|
||
ON DELETE CASCADE ON UPDATE CASCADE
|
||
) ENGINE=InnoDB;
|
||
SET FOREIGN_KEY_CHECKS=1;
|
||
UPDATE t3 SET payload='101' WHERE t3_id=1;
|
||
SET FOREIGN_KEY_CHECKS=0;
|
||
DROP TABLE t1;
|
||
DROP TABLE t2;
|
||
DROP TABLE t3;
|
||
DROP TABLE t4;
|
||
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
|