Mysql learning record (14) -- mode _ MySQL

Mysqlusetest1; ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql

mysql> use test1;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> set session sql_mode="ANSI";Query OK, 0 rows affected (0.01 sec)mysql> create table t (d datetime);Query OK, 0 rows affected (0.03 sec)mysql> insert into t values('2007-04-31');Query OK, 1 row affected, 1 warning (0.01 sec)mysql> select * from t;+---------------------+| d                   |+---------------------+| 0000-00-00 00:00:00 |+---------------------+1 row in set (0.00 sec)mysql> set session sql_mode="TRADITIONAL";Query OK, 0 rows affected (0.00 sec)mysql> insert into t values('2000-12-25');Query OK, 1 row affected (0.01 sec)mysql> select * from t;+---------------------+| d                   |+---------------------+| 0000-00-00 00:00:00 || 2000-12-25 00:00:00 |+---------------------+2 rows in set (0.00 sec)mysql> select @@sql_mode;+------------------------------------------------------------------------------------------------------------------------------------------------------+| @@sql_mode                                                                                                                                           |+------------------------------------------------------------------------------------------------------------------------------------------------------+| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |+------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> set sql_mode="";Query OK, 0 rows affected (0.00 sec)mysql> select @@sql_mode;+------------+| @@sql_mode |+------------+|            |+------------+1 row in set (0.00 sec)mysql> set session sql_mode="TRADITIONAL";Query OK, 0 rows affected (0.00 sec)mysql> insert into t values('2000-12-25');Query OK, 1 row affected (0.01 sec)mysql> select * from t;+---------------------+| d                   |+---------------------+| 0000-00-00 00:00:00 || 2000-12-25 00:00:00 || 2000-12-25 00:00:00 |+---------------------+3 rows in set (0.00 sec)mysql> select @@sql_mode;+------------------------------------------------------------------------------------------------------------------------------------------------------+| @@sql_mode                                                                                                                                           |+------------------------------------------------------------------------------------------------------------------------------------------------------+| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |+------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> set @@sql_mode="";Query OK, 0 rows affected (0.00 sec)mysql> select @@sql_mode;+------------+| @@sql_mode |+------------+|            |+------------+1 row in set (0.00 sec)mysql> set session sql_mode="ANSI";Query OK, 0 rows affected (0.00 sec)mysql> insert into t values('2000-12-25');Query OK, 1 row affected (0.01 sec)mysql> select * from t;+---------------------+| d                   |+---------------------+| 0000-00-00 00:00:00 || 2000-12-25 00:00:00 || 2000-12-25 00:00:00 || 2000-12-25 00:00:00 |+---------------------+4 rows in set (0.00 sec)mysql> set sql_mode="ANSI";Query OK, 0 rows affected (0.00 sec)mysql> drop table t;Query OK, 0 rows affected (0.02 sec)mysql> create table t ( i int);Query OK, 0 rows affected (0.02 sec)mysql> insert into t  values ( 9%0);Query OK, 1 row affected (0.00 sec)mysql> select * from t;+------+| i    |+------+| NULL |+------+1 row in set (0.00 sec)mysql> set session sql_mdoe = 'TRADITIONAL';ERROR 1193 (HY000): Unknown system variable 'sql_mdoe'mysql> set session sql_mode = 'TRADITIONAL';Query OK, 0 rows affected (0.00 sec)mysql> insert into t values(9%0);ERROR 1365 (22012): Division by 0mysql> set sql_mode = 'ansi';Query OK, 0 rows affected (0.00 sec)mysql> select @@sql_mode;+-------------------------------------------------------------+| @@sql_mode                                                  |+-------------------------------------------------------------+| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |+-------------------------------------------------------------+1 row in set (0.01 sec)mysql> drop table t;Query OK, 0 rows affected (0.01 sec)mysql> create table t (context varchar(20));Query OK, 0 rows affected (0.02 sec)mysql> insert into t value('\bw123');Query OK, 1 row affected (0.01 sec)mysql> select * from t;+---------+| context |+---------+|w123   |+---------+1 row in set (0.00 sec)mysql> insert into t value('\\bw123');Query OK, 1 row affected (0.01 sec)mysql> select * from t;+---------+| context |+---------+|w123   || \bw123  |+---------+2 rows in set (0.00 sec)mysql> select @@sql_mode;+-------------------------------------------------------------+| @@sql_mode                                                  |+-------------------------------------------------------------+| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |+-------------------------------------------------------------+1 row in set (0.00 sec)mysql> set sql_mode =     -> 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,NO_BACKSLASH_ESCAPES';Query OK, 0 rows affected (0.00 sec)mysql> select @@mode;ERROR 1193 (HY000): Unknown system variable 'mode'mysql> select @@sql_mode;+----------------------------------------------------------------------------------+| @@sql_mode                                                                       |+----------------------------------------------------------------------------------+| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,NO_BACKSLASH_ESCAPES |+----------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> select * from t;+---------+| context |+---------+|w123   || \bw123  |+---------+2 rows in set (0.00 sec)mysql> set sql_mode = 'ansi';Query OK, 0 rows affected (0.00 sec)mysql> select @@sql_mode;+-------------------------------------------------------------+| @@sql_mode                                                  |+-------------------------------------------------------------+| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |+-------------------------------------------------------------+1 row in set (0.00 sec)mysql> select 'bw123'||'2008';+-------------------+| 'bw123'||'2008' |+-------------------+| bw1232008       |+-------------------+1 row in set (0.00 sec)mysql> show create table emp \G;*************************** 1. row ***************************       Table: empCreate Table: CREATE TABLE "emp" (  "ename" varchar(10) DEFAULT NULL,  "hiredate" date DEFAULT NULL,  "sal" decimal(10,2) DEFAULT NULL,  "deptno" int(2) DEFAULT NULL)1 row in set (0.00 sec)ERROR: No query specifiedmysql> set setssion sql_mode = 'NO_TABLE_OPTIONS';ERROR 1193 (HY000): Unknown system variable 'setssion'mysql> set session sql_mode = 'NO_TABLE_OPTIONS';Query OK, 0 rows affected (0.00 sec)mysql> show create table emp \G;*************************** 1. row ***************************       Table: empCreate Table: CREATE TABLE `emp` (  `ename` varchar(10) DEFAULT NULL,  `hiredate` date DEFAULT NULL,  `sal` decimal(10,2) DEFAULT NULL,  `deptno` int(2) DEFAULT NULL)1 row in set (0.00 sec)ERROR: No query specified