ITPub博客

首页 > 数据库 > MySQL > mysql sql mode及相关问题

mysql sql mode及相关问题

原创 MySQL 作者:oraclesuperman 时间:2015-07-17 15:03:39 0 删除 编辑

mysql> select @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)


mysql> drop table t;
Query OK, 0 rows affected (0.02 sec)

mysql> create table t(name varchar(4));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t values('aaaaaaaaaaa');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> set session sql_mode='real_as_float';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values('aaaaaaaaaaa');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)


mysql> set session sql_mode='ANSI';
Query OK, 0 rows affected (0.00 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.02 sec)

mysql> create table t1 (d datetime);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t values('2015-07-17');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t;
+------+
| name |
+------+
| aaaa |
| 2015 |
| 2015 |
+------+
3 rows in set (0.00 sec)

mysql> select * from t1;
Empty set (0.00 sec)

mysql> insert into t1 values('2015-07-17');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+---------------------+
| d                   |
+---------------------+
| 2015-07-17 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.02 sec)

mysql> create table t1 (d datetime);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1 values('2015-07-17');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+---------------------+
| d                   |
+---------------------+
| 2015-07-17 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 t1 values('2015-07-17');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+---------------------+
| d                   |
+---------------------+
| 2015-07-17 00:00:00 |
| 2015-07-17 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)


mysql> set session sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values('2015-07-17');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+---------------------+
| d                   |
+---------------------+
| 2015-07-17 00:00:00 |
| 2015-07-17 00:00:00 |
| 2015-07-17 00:00:00 |
+---------------------+
3 rows in set (0.00 sec)


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29313086/viewspace-1736291/,如需转载,请注明出处,否则将追究法律责任。

下一篇: mysql的常用工具
请登录后发表评论 登录
全部评论

注册时间:2015-03-20

  • 博文量
    178
  • 访问量
    399001