Home > Database > Mysql Tutorial > mysql学习记录(十四)--模式_MySQL

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

WBOY
Release: 2016-05-30 17:10:46
Original
1032 people have browsed it

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
Copy after login

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template