mysql学习记录(十四)--模式

来源:互联网 时间:1970-01-01

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


相关阅读:
Top