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-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 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("\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: 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

 mysql> use test1;
Reading table information for co