• 在线客服
    • 蝉知客服
为什么在有的服务器上禅道、蝉知、然之安装会报错? 之理解MySQL的SQL_MODE
2018-02-28 10:58:06
liyanling
  • 访问次数: 64
  • 注册日期: 2017-06-12
  • 最后登录: 2018-06-25
  • 当前积分: 585
  • 门派等级: /score-rule.html

最近用 蝉知CMS 建站比较多,感觉蛮顺手的,但在给客户安装的时候却会出现安装报错,其原因也很简单

查看了一下他们的install.sql文件中,有些时间字段的默认值是0000-00-00 00:00:00 ... 额,我个人习惯将时间值存int类型,这样程序处理上还有查询计算上会方便一些,可能他们为了在看数据库的时候方便一目了然的认出时间吧...不管它的初衷,要改程序不太可能了,以后也不好升级.我还是改我的数据库的兼容模式吧

在MySQL5.5以后,其默认使用的是一种严格模式,也就是说,像禅知用的这种默认值的方式就会报错.SO,我们今天来说一说这个SQL_MODEL的相关的东东

SQL_MODE:通过对其正确的设置可以完成一些约束检查的工作,设置时,可在配置文件my.cnf或my.ini中进行,也可在客户端中进行,并可分别进行全局的设置或当前会话的设置。

查看SQL_MODE设置情况():

mysql> SHOW VARIABLES LIKE 
                    'SQL_MODE';
+---------------+----------------------------------------------------------------+ 
                    | Variable_name | Value 
                    |
+---------------+----------------------------------------------------------------+
| sql_mode 
                    | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +---------------+----------------------------------------------------------------+ 
                    1 row 
                    in set (
                    0.
                    03 sec)

或者(全局)

mysql> SELECT @@global.SQL_MODE;
+----------------------------------------------------------------+ 
                    | @@global.SQL_MODE                                              | +----------------------------------------------------------------+ 
                    | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +----------------------------------------------------------------+ 
                    1 row 
                    in set (
                    0.
                    00 sec)

亦或(当前会话)

mysql> SELECT @@session.SQL_MODE;
+----------------------------------------------------------------+ 
                    | @@session.SQL_MODE                                             | +----------------------------------------------------------------+ 
                    | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +----------------------------------------------------------------+ 
                    1 row 
                    in set (
                    0.
                    00 sec)

亦或

mysql> SELECT @@SQL_MODE;
+----------------------------------------------------------------+ 
                    | @@session.SQL_MODE                                             | +----------------------------------------------------------------+ 
                    | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +----------------------------------------------------------------+ 
                    1 row 
                    in set (
                    0.
                    00 sec)

设置方法很简单

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

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

介绍一下几种模式

STRICT_TRANS_TALES(严格模式):
只对支持事务的表启用严格模式

STRICT_ALL_TABLES(严格模式):
对所有引擎的表都启用严格模式

ALLOW_INVALID_DATES:
不完全对日期合法性作检查,只检查月份是否在1~12,日期是否在1~31之间;仅对DATE和DATETIME有效,而对TIMESTAMP无效,因为TIMESTAMP总要求一个合法的输入。

ANSI_QUOTES:
启用后,不能用双引号来引用字符串,因为"(双引号)将被解释为标识符

mysql> 
                    
                    CREATE TABLE 
                    
                    
                    a
                     (
                    
                    
                     a 
                    
                    
                    
                    char
                    
                    (
                    
                    
                    
                    5
                    ));
Query OK, 
                    0 
                    
                    rows 
                    
                    
                    affected
                     (
                    
                    
                    
                    
                    0.26
                    
                     sec
                    )

mysql> INSERT INTO a SELECT 'abc';
Query OK, 
                    1 
                    
                    row 
                    
                    
                    affected
                     (
                    
                    
                    
                    
                    0.05
                    
                     sec
                    )
Records: 1 Duplicates: 0 Warnings: 0

mysql> SET SQL_MODE=
                    'ANSI_QUOTES';
Query OK, 
                    0 
                    
                    rows 
                    
                    
                    affected
                     (
                    
                    
                    
                    
                    0.00
                    
                     sec
                    )

mysql> INSERT INTO a SELECT "abc";
ERROR 
                    1054 (
                    42S22): Unknown column 
                    'abc' 
                    in 
                    'field list' mysql> SELECT @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
| ANSI_QUOTES        |
+--------------------+ 
                    1 
                    
                    row 
                    
                    
                    in
                     
                    
                    
                    set
                     (
                    
                    
                    
                    
                    0.00
                    
                     sec
                    ) 

ERROR_FOR_DIVISION_BY_ZERO:
启用后,在insert或update过程中,若数据被零除(或MOD(x,0),则产生错误,若未启用,则产生警告,数据被零除时系统返回NULL。

HIGH_NOT_PRECEDENCE:
启用后,可获得以前旧版本的优先级:
NOT a BETWEEN b AND c这个语句:
now: NOT (a BETWEEN b AND c)
before: (NOT a) BETWEEN b AND c

IGNORE_SPACE:
启用后,忽略函数名和括号"("之间空格,要访问保存为关键字的数据库名,表名,列名时,需启用。

mysql> SELECT NOW();
ERROR 
                    1630 (
                    42000): FUNCTION test.NOW does 
                    not exist. Check the 
                    'Function Name Parsing and Resolution' section 
                    in the Reference Manual
mysql> SET SQL_MODE=
                    'IGNORE_SPACE';
Query OK, 
                    0 rows affected (
                    0.
                    00 sec)

mysql> SELECT NOW();
+---------------------+ 
                    | NOW ()              | +---------------------+ 
                    | 2015-10-08 17:49:35 | +---------------------+ 
                    1 row 
                    in set (
                    0.
                    00 sec)

NO_AUTO_CREATE_USER:
禁止GRANT创建密码为空的用户。

NO_AUTO_VALUE_ON_ZERO:
在自增长的列中插入0或NULL将不会是下一个自增长值。

O_BACKSLASH_ESCAPES:
反斜杠\作为普通字符而非转义字符

mysql> SET SQL_MODE=
                    '';
Query OK, 
                    0 rows affected (
                    0.
                    00 sec)

mysql> SELECT 
                    '\\';
+---+ 
                    | \ | +---+ 
                    | \ | +---+ 
                    1 row 
                    in set (
                    0.
                    00 sec)

mysql> SET SQL_MODE=
                    'NO_BACKSLASH_ESCAPES';
Query OK, 
                    0 rows affected (
                    0.
                    00 sec)

mysql> SELECT 
                    '\\';
+----+ 
                    | \\ | +----+ 
                    | \\ | +----+ 
                    1 row 
                    in set (
                    0.
                    00 sec)

NO_DIR_IN_CREATE:
在创建表时忽略所有index directory和data directory的选项。

NO_ENGINE_SUBSTITUTION:
启用后,若需要的存储引擎被禁用或未编译,则抛出错误;未启用时将用默认的存储引擎代替,并抛出一个异常。

NO_UNSIGNED_SUBSTRACTION:
启用后,两个UNSIGNED类型相减返回SIGNED类型。

NO_ZERO_DATE:
启用后,不允许插入"0000-00-00 00:00:00"形如此类的零日期,这将抛出一个错误,若未启用,则可插入但仅会抛出一个警告。

NO_ZERO_IN_DATE:
启用后,不允许月份和日期为零,和NO_ZERO_DATE一起启用,如"1999-01-00"将抛出错误而非警告。
若单独启用本项,则会抛出warning,然后插入如“0000-00-00 00:00:00”。

ONLY_FULL_GROUP_BY:
对于GROUP BY聚合操作,若select中的列没有在group by中出现,那么这句SQL是不合法的。

PAD_CHAR_TO_FULL_LENGTH:
启用后,对于CHAR类型将不会截断空洞数据

mysql> CREATE TABLE a ( a char(
                    10), b varchar(
                    10));
Query OK, 
                    0 rows affected (
                    0.
                    29 sec)

mysql> INSERT INTO a SELECT 
                    'a',
                    'b';
Query OK, 
                    1 row affected (
                    0.
                    03 sec) 
                    Records: 
                    1 
                    Duplicates: 
                    0 
                    Warnings: 
                    0;

mysql> SELECT CHAR_LENGTH(a), CHAR_LENGTH(b) FROM a;
+----------------+----------------+ 
                    | CHAR_LENGTH(a)  | CHAR_LENGTH(b) 
                    |
+----------------+----------------+
| 
                    1 
                    | 1              | +----------------+----------------+ 
                    1 row 
                    in set (
                    0.
                    01 sec)

mysql> SET SQL_MODE=
                    'PAD_CHAR_TO_FULL_LENGTH';
Query OK, 
                    0 rows affected (
                    0.
                    00 sec)

mysql> SELECT CHAR_LENGTH(a), CHAR_LENGTH(b) FROM a;
+----------------+----------------+ 
                    | CHAR_LENGTH(a) | CHAR_LENGTH(b) 
                    |
+----------------+----------------+
| 
                    10 
                    | 1              | +----------------+----------------+ 
                    1 row 
                    in set (
                    0.
                    00 sec)

PIPES_AS_CONCAT:
将"||"视为连接操作符而非"或运算符"。

REAL_AS_FL OA T:
将REAL视为FLOAT的同义词而非DOUBLE的同义词。

蝉知相关产品的解决办法也就很明了啦~~:

先查看本地的模式

mysql> SELECT @@global.SQL_MODE;
+---------------------------------------------------------------------------------------------+ 
                    | @@global.SQL_MODE                                                                           | +---------------------------------------------------------------------------------------------+ 
                    | STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +---------------------------------------------------------------------------------------------+ 
                    1 row 
                    in set (
                    0.
                    00 sec)

然后去掉NO_ZERO_DATE和NO_ZERO_IN_DATE

                    SET 
                    global sql_mode=
                    'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

当然你也可以在my.cnf中配置

                    [mysqld] 
                    sql-mode=
                    "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" 

这样就可以不用每次设置啦

100
沙发
2018-03-02 16:21:09
春哥
  • 访问次数: 2646
  • 注册日期: 2013-08-15
  • 最后登录: 2018-08-13
  • 当前积分: 3781
  • 门派等级: /score-rule.html
好文章:)
1/1