mysql 学习记录（十四）--模式

mysql> use test1; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> 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⑶1'); 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⑴2⑵5'); Query OK, 1 row affected (0.01 sec) mysql> select * from t; +---------------------+ | d | +---------------------+ | 0000-00-00 00:00:00 | | 2000⑴2⑵5 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⑴2⑵5'); Query OK, 1 row affected (0.01 sec) mysql> select * from t; +---------------------+ | d | +---------------------+ | 0000-00-00 00:00:00 | | 2000⑴2⑵5 00:00:00 | | 2000⑴2⑵5 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⑴2⑵5'); Query OK, 1 row affected (0.01 sec) mysql> select * from t; +---------------------+ | d | +---------------------+ | 0000-00-00 00:00:00 | | 2000⑴2⑵5 00:00:00 | | 2000⑴2⑵5 00:00:00 | | 2000⑴2⑵5 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 0 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.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('w123'); 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('w123'); Query OK, 1 row affected (0.01 sec) mysql> select * from t; +---------+ | context | +---------+ |w123 | | w123 | +---------+ 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 | | w123 | +---------+ 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: emp Create 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 mysql> 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: emp Create 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