drop table if exists t1,t2,v1,v2; drop view if exists t1,t2,v1,v2; CREATE TABLE `t1` ( a int not null auto_increment, `pseudo` varchar(35) character set latin2 NOT NULL default '', `email` varchar(60) character set latin2 NOT NULL default '', PRIMARY KEY (a), UNIQUE KEY `email` USING BTREE (`email`) ) ENGINE=HEAP CHARSET=latin1 ROW_FORMAT DYNAMIC; set @@sql_mode=""; show variables like 'sql_mode'; Variable_name Value sql_mode show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL AUTO_INCREMENT, `pseudo` varchar(35) CHARACTER SET latin2 NOT NULL DEFAULT '', `email` varchar(60) CHARACTER SET latin2 NOT NULL DEFAULT '', PRIMARY KEY (`a`), UNIQUE KEY `email` (`email`) USING BTREE ) ENGINE=MEMORY DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC set @@sql_mode="ansi_quotes"; show variables like 'sql_mode'; Variable_name Value sql_mode ANSI_QUOTES show create table t1; Table Create Table t1 CREATE TABLE "t1" ( "a" int(11) NOT NULL AUTO_INCREMENT, "pseudo" varchar(35) CHARACTER SET latin2 NOT NULL DEFAULT '', "email" varchar(60) CHARACTER SET latin2 NOT NULL DEFAULT '', PRIMARY KEY ("a"), UNIQUE KEY "email" ("email") USING BTREE ) ENGINE=MEMORY DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC set @@sql_mode="no_table_options"; show variables like 'sql_mode'; Variable_name Value sql_mode NO_TABLE_OPTIONS show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL AUTO_INCREMENT, `pseudo` varchar(35) CHARACTER SET latin2 NOT NULL DEFAULT '', `email` varchar(60) CHARACTER SET latin2 NOT NULL DEFAULT '', PRIMARY KEY (`a`), UNIQUE KEY `email` (`email`) USING BTREE ) set @@sql_mode="no_key_options"; show variables like 'sql_mode'; Variable_name Value sql_mode NO_KEY_OPTIONS show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL AUTO_INCREMENT, `pseudo` varchar(35) CHARACTER SET latin2 NOT NULL DEFAULT '', `email` varchar(60) CHARACTER SET latin2 NOT NULL DEFAULT '', PRIMARY KEY (`a`), UNIQUE KEY `email` (`email`) ) ENGINE=MEMORY DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC set @@sql_mode="no_field_options,mysql323,mysql40"; show variables like 'sql_mode'; Variable_name Value sql_mode NO_FIELD_OPTIONS,MYSQL323,MYSQL40,HIGH_NOT_PRECEDENCE show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `pseudo` varchar(35) NOT NULL DEFAULT '', `email` varchar(60) NOT NULL DEFAULT '', PRIMARY KEY (`a`), UNIQUE KEY `email` (`email`) ) TYPE=HEAP ROW_FORMAT=DYNAMIC set sql_mode="postgresql,oracle,mssql,db2,maxdb"; select @@sql_mode; @@sql_mode PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER show create table t1; Table Create Table t1 CREATE TABLE "t1" ( "a" int(11) NOT NULL, "pseudo" varchar(35) CHARACTER SET latin2 NOT NULL DEFAULT '', "email" varchar(60) CHARACTER SET latin2 NOT NULL DEFAULT '', PRIMARY KEY ("a"), UNIQUE KEY "email" ("email") ) drop table t1; CREATE TABLE t1 ( a char(10), b char(10) collate latin1_bin, c binary(10) ) character set latin1; set @@sql_mode=""; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` char(10) DEFAULT NULL, `b` char(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `c` binary(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 set @@sql_mode="mysql323"; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` char(10) DEFAULT NULL, `b` char(10) binary DEFAULT NULL, `c` binary(10) DEFAULT NULL ) TYPE=MyISAM set @@sql_mode="mysql40"; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` char(10) DEFAULT NULL, `b` char(10) binary DEFAULT NULL, `c` binary(10) DEFAULT NULL ) TYPE=MyISAM drop table t1; set session sql_mode = ''; create table t1 ( min_num dec(6,6) default .000001); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `min_num` decimal(6,6) DEFAULT '0.000001' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1 ; set session sql_mode = 'IGNORE_SPACE'; create table t1 ( min_num dec(6,6) default 0.000001); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `min_num` decimal(6,6) DEFAULT '0.000001' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1 ; create table t1 ( min_num dec(6,6) default .000001); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `min_num` decimal(6,6) DEFAULT '0.000001' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1 ; set @@SQL_MODE=NULL; ERROR 42000: Variable 'sql_mode' can't be set to the value of 'NULL' set session sql_mode=ansi; create table t1 (f1 integer auto_increment primary key, f2 timestamp default current_timestamp on update current_timestamp); show create table t1; Table Create Table t1 CREATE TABLE "t1" ( "f1" int(11) NOT NULL AUTO_INCREMENT, "f2" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY ("f1") ) set session sql_mode=no_field_options; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `f1` int(11) NOT NULL, `f2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`f1`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; SET @OLD_SQL_MODE=@@SQL_MODE, @@SQL_MODE=''; show local variables like 'SQL_MODE'; Variable_name Value sql_mode CREATE TABLE t1 (p int not null auto_increment, a varchar(20), primary key(p)); INSERT t1 (a) VALUES ('\\'), ('\n'), ('\b'), ('\r'), ('\t'), ('\x'), ('\a'), ('\aa'), ('\\a'), ('\\aa'), ('_'), ('\_'), ('\\_'), ('\\\_'), ('\\\\_'), ('%'), ('\%'), ('\\%'), ('\\\%'), ('\\\\%') ; SELECT p, hex(a) FROM t1; p hex(a) 1 5C 2 0A 3 08 4 0D 5 09 6 78 7 61 8 6161 9 5C61 10 5C6161 11 5F 12 5C5F 13 5C5F 14 5C5C5F 15 5C5C5F 16 25 17 5C25 18 5C25 19 5C5C25 20 5C5C25 delete from t1 where a in ('\n','\r','\t', '\b'); select masks.p, masks.a as mask, examples.a as example from t1 as masks left join t1 as examples on examples.a LIKE masks.a order by masks.p, example; p mask example 1 \ \ 6 x x 7 a a 8 aa aa 9 \a a 10 \aa aa 11 _ % 11 _ a 11 _ x 11 _ \ 11 _ _ 12 \_ _ 13 \_ _ 14 \\_ \% 14 \\_ \% 14 \\_ \a 14 \\_ \_ 14 \\_ \_ 15 \\_ \% 15 \\_ \% 15 \\_ \a 15 \\_ \_ 15 \\_ \_ 16 % % 16 % a 16 % aa 16 % x 16 % \ 16 % \% 16 % \% 16 % \a 16 % \aa 16 % \\% 16 % \\% 16 % \\_ 16 % \\_ 16 % \_ 16 % \_ 16 % _ 17 \% % 18 \% % 19 \\% \ 19 \\% \% 19 \\% \% 19 \\% \a 19 \\% \aa 19 \\% \\% 19 \\% \\% 19 \\% \\_ 19 \\% \\_ 19 \\% \_ 19 \\% \_ 20 \\% \ 20 \\% \% 20 \\% \% 20 \\% \a 20 \\% \aa 20 \\% \\% 20 \\% \\% 20 \\% \\_ 20 \\% \\_ 20 \\% \_ 20 \\% \_ DROP TABLE t1; SET @@SQL_MODE='NO_BACKSLASH_ESCAPES'; show local variables like 'SQL_MODE'; Variable_name Value sql_mode NO_BACKSLASH_ESCAPES CREATE TABLE t1 (p int not null auto_increment, a varchar(20), primary key(p)); INSERT t1 (a) VALUES ('\\'), ('\n'), ('\b'), ('\r'), ('\t'), ('\x'), ('\a'), ('\aa'), ('\\a'), ('\\aa'), ('_'), ('\_'), ('\\_'), ('\\\_'), ('\\\\_'), ('%'), ('\%'), ('\\%'), ('\\\%'), ('\\\\%') ; SELECT p, hex(a) FROM t1; p hex(a) 1 5C5C 2 5C6E 3 5C62 4 5C72 5 5C74 6 5C78 7 5C61 8 5C6161 9 5C5C61 10 5C5C6161 11 5F 12 5C5F 13 5C5C5F 14 5C5C5C5F 15 5C5C5C5C5F 16 25 17 5C25 18 5C5C25 19 5C5C5C25 20 5C5C5C5C25 delete from t1 where a in ('\n','\r','\t', '\b'); select masks.p, masks.a as mask, examples.a as example from t1 as masks left join t1 as examples on examples.a LIKE masks.a order by masks.p, example; p mask example 1 \\ \\ 6 \x \x 7 \a \a 8 \aa \aa 9 \\a \\a 10 \\aa \\aa 11 _ % 11 _ _ 12 \_ \% 12 \_ \a 12 \_ \x 12 \_ \\ 12 \_ \_ 13 \\_ \\% 13 \\_ \\a 13 \\_ \\_ 14 \\\_ \\\% 14 \\\_ \\\_ 15 \\\\_ \\\\% 15 \\\\_ \\\\_ 16 % % 16 % \% 16 % \a 16 % \aa 16 % \x 16 % \\ 16 % \\% 16 % \\a 16 % \\aa 16 % \\\% 16 % \\\\% 16 % \\\\_ 16 % \\\_ 16 % \\_ 16 % \_ 16 % _ 17 \% \% 17 \% \a 17 \% \aa 17 \% \x 17 \% \\ 17 \% \\% 17 \% \\a 17 \% \\aa 17 \% \\\% 17 \% \\\\% 17 \% \\\\_ 17 \% \\\_ 17 \% \\_ 17 \% \_ 18 \\% \\ 18 \\% \\% 18 \\% \\a 18 \\% \\aa 18 \\% \\\% 18 \\% \\\\% 18 \\% \\\\_ 18 \\% \\\_ 18 \\% \\_ 19 \\\% \\\% 19 \\\% \\\\% 19 \\\% \\\\_ 19 \\\% \\\_ 20 \\\\% \\\\% 20 \\\\% \\\\_ DROP TABLE t1; SET @@SQL_MODE='NO_BACKSLASH_ESCAPES'; SELECT 'a\\b', 'a\\\"b', 'a''\\b', 'a''\\\"b'; a\\b a\\\"b a'\\b a'\\\"b a\\b a\\\"b a'\\b a'\\\"b SELECT "a\\b", "a\\\'b", "a""\\b", "a""\\\'b"; a\\b a\\\'b a"\\b a"\\\'b a\\b a\\\'b a"\\b a"\\\'b SET @@SQL_MODE=''; SELECT 'a\\b', 'a\\\"b', 'a''\\b', 'a''\\\"b'; a\b a\"b a'\b a'\"b a\b a\"b a'\b a'\"b SELECT "a\\b", "a\\\'b", "a""\\b", "a""\\\'b"; a\b a\'b a"\b a"\'b a\b a\'b a"\b a"\'b SET @@SQL_MODE=''; create function `foo` () returns int return 5; show create function `foo`; Function sql_mode Create Function foo CREATE DEFINER=`root`@`localhost` FUNCTION `foo`() RETURNS int(11) return 5 SET @@SQL_MODE='ANSI_QUOTES'; show create function `foo`; Function sql_mode Create Function foo CREATE DEFINER=`root`@`localhost` FUNCTION `foo`() RETURNS int(11) return 5 drop function `foo`; create function `foo` () returns int return 5; show create function `foo`; Function sql_mode Create Function foo ANSI_QUOTES CREATE DEFINER="root"@"localhost" FUNCTION "foo"() RETURNS int(11) return 5 SET @@SQL_MODE=''; show create function `foo`; Function sql_mode Create Function foo ANSI_QUOTES CREATE DEFINER="root"@"localhost" FUNCTION "foo"() RETURNS int(11) return 5 drop function `foo`; SET @@SQL_MODE=''; create table t1 (a int); create table t2 (a int); create view v1 as select a from t1; show create view v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` SET @@SQL_MODE='ANSI_QUOTES'; show create view v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER="root"@"localhost" SQL SECURITY DEFINER VIEW "v1" AS select "t1"."a" AS "a" from "t1" create view v2 as select a from t2 where a in (select a from v1); drop view v2, v1; drop table t1, t2; select @@sql_mode; @@sql_mode ANSI_QUOTES set sql_mode=2097152; select @@sql_mode; @@sql_mode STRICT_TRANS_TABLES set sql_mode=16384+(65536*4); select @@sql_mode; @@sql_mode REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_TABLE_OPTIONS,ANSI set sql_mode=2147483648; ERROR 42000: Variable 'sql_mode' can't be set to the value of '2147483648' select @@sql_mode; @@sql_mode REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_TABLE_OPTIONS,ANSI SET @@SQL_MODE=@OLD_SQL_MODE;