mysql学习记录（十四）--模式_MySQL
程序员文章站
2022-06-01 17:56:24
...
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