`
Sarah-Brightman
  • 浏览: 75042 次
  • 性别: Icon_minigender_2
  • 来自: 上海
社区版块
存档分类
最新评论

详解mysql数据库sql_mode模式

阅读更多
今天在学习中遇到sql_mode这个东西,曾经一直没用过,查找相关文章先贴出来,以备后用。

mysql可以运行在不同sql mode模式下面,sql mode模式定义了mysql应该支持的sql语法,数据校验等!

查看默认的sql mode模式:
select @@sql_mode;
我的数据库是:
STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
在此模式下面,如果插入的数据的长度大于定义的长度,那么就会报错!

set session sql_mode='REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI';
在这种模式下面:插入的数据的长度大于定义的时候,就会截取,并警告,但是可以插入进去
session表示只在本次中有效
global:表示在本次连接中不生效,而对于新的连接就生效

启用NO_BACKSLASH_ESCAPES模式,使反斜线成为普通字符,在导入数据时候,如果数据中有反斜线,启用这个模式是个不错的选择

启用PIPES_AS_CNCAT模式,将||看成是普通字符串

常用的sql mode:
sql mode值 说明
ANSI: 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE和ANSI组合',这种模式使语法和行为更符合标准的sql
STRICT_TRANS_TABLES : 使用与事务和非事务表,严格模式
TRADITIONAL :也是严格模式,对于插入不正确的值给出错误而不是警告。用在事务时,只要发生错误就立即回滚

在Mysql5.0以下,默认的sql mode(sql mode参数)有:real_as_float,pipes_as_concat,ansi_quotes,gnore_space和ANSI。在这些模式下可以插入超过字段定义长度的数据,或是在字段中没有定义的元素数据(如,enum)。不过在插入后会有一个warning(可以用 show warnings来查看)。
在Mysql5.0以上版本中,有三种sql mode模式(ANSI、TRADITIONAL和STRICT_TRANS_TABLES(严格模式))可以用来解决以下问题:
(1). 通过设置不同的sql mode,可以在不同严格程序进行数据校验,有效地保证了数据准确性.
(2).通过设置sql mode为ANSI模式,来保证大多数SQL符合标准SQL的语法,这样在不同数据库之间迁移时,不需要对业务修改太多.
通过设置sql mode为STRICT_TRANS_TABLES(严格模式)来实现数据的严格校检,使错误数据不能插入,从而保证数据准确性。TRADITIONAL 模式也属于严格模式,同样可以实现严格校检,使错误数据不能插入,从而保证数据准确性。不过在这种模式MAX(X,0)返回的结果是NULL,所以在包含有MAX的运算中根据实际情况设定好sql mode.
ENUM是一个字符串对象,其值来自表创建时在列规定中显式枚举的一列值。在某些情况下,ENUM值也可以为空字符串('')或NULL:如果你将一个非法值插入ENUM(也就是说,允许的值列之外的字符串),将插入空字符串以作为特殊错误值。该字符串与“普通” 空字符串不同,该字符串有数值值0。
如果将ENUM列声明为允许NULL,NULL值则为该列的一个有效值,并且 默认值为NULL。如果ENUM列被声明为NOT NULL,其默认值为允许的值列的第1个元素。
(1)sql mode为ANSI
mysql> create table test(id bigint(20) auto_increment primary key, browsertype enum('ie','firefox','other'));
mysql> insert into test(browsertype) values('ie') ;
Query OK, 1 row affected (0.07 sec)
mysql> insert into test(browsertype) values('maxthon') ;
Query OK, 1 row affected (0.03 sec)
mysql> show warnings;(数据虽然成功insert,但有warning)
+---------+------+--------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------+
| Warning | 1265 | Data truncated for column 'browsertype' at row 1 |
+---------+------+--------------------------------------------------+
1 row in set (0.01 sec)
mysql> select * from test;
+----+-------------+
| id | browsertype |
+----+-------------+
| 1 | ie |
| 2 | |
+----+-------------+
2 rows in set (0.01 sec)
(2)使用严格模式(STRICT_TRANS_TABLES)
mysql> set session sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@sql_mode;
+---------------------+
| @@sql_mode |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
1 row in set (0.00 sec)
mysql> insert into test(browsertype) values('maxthon') ;
ERROR 1265: Data truncated for column 'browsertype' at row 1

mysql> insert into test(browsertype) values('firefox') ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+----+-------------+
| id | browsertype |
+----+-------------+
| 1 | ie |
| 2 | |
| 3 | firefox |
+----+-------------+
3 rows in set (0.00 sec)
(3) TRADITIONAL模式
mysql> create table t11 (i int);
Query OK, 0 rows affected (0.02 sec)
mysql> set sql_mode='ANSI';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t11 values(9%0);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t11;
+------+
| i |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
mysql> set sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t11 values(9%0);
ERROR 1365: Division by 0
mysql> show warnings;
+-------+------+---------------+
| Level | Code | Message |
+-------+------+---------------+
| Error | 1365 | Division by 0 |
+-------+------+---------------+
1 row in set (0.02 sec)
分享到:
评论

相关推荐

    mysql中的sql_mode模式实例详解

    本文实例讲述了mysql中的sql_mode模式。分享给大家供大家参考,具体如下: mysql数据库的中有一个环境变量sql_mode,定义了mysql应该支持的sql语法,数据校验等!我们可以通过以下方式查看当前数据库使用的sql_mode:...

    关于MySQL的sql_mode合理设置详解

    MySQL的`sql_mode`是数据库服务器的一个关键配置项,它定义了MySQL如何处理不符合标准SQL的行为,以及在数据验证和插入方面遵循何种规则。合理的设置`sql_mode`可以帮助确保数据的准确性,避免潜在的问题,特别是在...

    解决MySQL 5.7.9版本sql_mode=only_full_group_by问题

    通过阅读MySQL文档和相关的技术文章,如“Mysql之SQL Mode用法详解”、“MySQL5.7中的sql_mode默认值带来的坑及解决方法”等,可以深入理解各种SQL模式的作用和应用场景,从而更好地优化和维护你的数据库系统。

    MySQL中SQL Mode的查看与设置详解

    MySQL中的SQL Mode是一种非常重要的配置,它决定了数据库服务器在处理SQL语句时的行为。SQL Mode定义了一组规则,这些规则会影响MySQL如何解释和执行SQL语句,包括数据验证、默认值处理、日期时间格式以及一些兼容性...

    详解Mysql数据库date, datetime类型设置0000-00-00默认值(default)报错问题

    MySQL数据库在5.7版本之后,...理解并正确配置`sql_mode`对于确保MySQL数据库的数据完整性和一致性至关重要,尤其是在升级到新版本或迁移数据库时。在设置`sql_mode`时,需要根据具体的应用场景和数据需求进行权衡。

    MySQL中slave_exec_mode参数详解

    在测试环境中,例如Percona MySQL 5.7,如果你的复制模式设为ROW,并且没有启用GTID,你可以观察到`slave_exec_mode`的效果。当从库试图执行主库的写入事件(如插入一条已存在的记录)时,如果`slave_exec_mode`设置...

    Mysql之SQL Mode用法详解

    SQL Mode允许MySQL在不同的模式下运行,这些模式决定了服务器支持的SQL语法和执行的数据检查级别。例如,某些模式可能要求更严格的SQL语法,或者在插入不符合列定义的数据时抛出错误,而非自动转换或截断数据。 2....

    PhpMysql数据库备份脚本.doc

    7. **SQL模式设置**:在MySQL 5.0及以上版本,可以使用`SET sql_mode=''`来清除当前的SQL模式,以避免可能影响备份操作的特定模式。 8. **查询执行**:`query()`函数用于执行SQL语句。在本脚本中,还包含了一个方法...

    MySQL模式 Strict Mode知识点详解

    MySQL模式中的Strict Mode是一个重要的配置选项,它对数据库操作的行为有着显著的影响,特别是在数据完整性方面。严格模式(Strict Mode)旨在确保数据输入的准确性和一致性,遵循更严格的SQL标准,从而减少潜在的...

    中国省市区三级数据库表

    40101 SET @OLD_SQL_MODE=@@SQL_MODE */` 和 `SET SQL_MODE=''` 是SQL提示符,用于在不同版本的MySQL中保持兼容性,这里清空了当前的SQL模式。 - `/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES */` 和 `SET SQL_NOTES=...

    MySQL数据库详细教程

    - `sql_mode`: SQL执行模式。 - `max_connections`: 最大连接数。 - `default_storage_engine`: 默认存储引擎。 #### 五、MySQL安装步骤 - **下载安装包**: - 访问MySQL官方网站(`...

Global site tag (gtag.js) - Google Analytics