MySQL sql_mode設定

標籤：default   配置   csdn   int   www.   comment   查看   engine   機率   

在MySQL5.7版本中建立表

CREATE TABLE `investor_seat` (  `id` int(11) NOT NULL AUTO_INCREMENT ,  `investorId` int(11) NOT NULL  COMMENT ‘投資人id‘,  `seatId` int(11) NOT NULL  COMMENT ‘席位id‘,  `maybe` float NOT NULL COMMENT ‘席位歸屬某幫派或者某人的機率‘,  `investorName` varchar(28) NOT NULL COMMENT ‘投資人名含機構‘,  `seatName` varchar(108) NOT NULL COMMENT ‘席位名‘,  `status` TINYINT NOT NULL DEFAULT 0 COMMENT ‘投資人席位狀態‘,  `addTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  `updateTime` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (`id`)) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARSET = utf8;

注意updateTime欄位，會在建立的時候報錯：

[Err] 1067 - Invalid default value for ‘updateTime‘

尋找了原因，是要設定sql_mode

查看MySQL的sql_mode配置：

mysql> show variables like ‘sql_mode‘;+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+| Variable_name | Value                                    |+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+1 row in set, 1 warning (0.00 sec)

將mysql安裝檔案裡面的my-default.ini(windows)//etc/my.cnf(Linux)裡面添加設定：

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

 

http://www.nowck.com/archives/70.html

http://blog.csdn.net/wyzxg/article/details/8787878

MySQL sql_mode設定