Incorrect table definition there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

这个问题是在创建表的时候，同时设置了两个默认的时间戳，比如下面的sql：

create table table_name
(col1 int(5) auto_increment primary key, 
col2 varchar(300), 
col3 varchar(500), 
col4 int(3), 
col5 tinyint(2), 
col6 timestamp default current_timestamp, 
col7 timestamp default current_timestamp on update current_timestamp, 
col8 tinyint(1) default 0, 
col9 tinyint(1) default 1);

这样就会执行sql的时候，就会出现这样的错误。

然后农成谷歌之后，就发现了原来是版本的问题导致的。mysql官方的解释是：

MYSQL 5.5

One TIMESTAMP column in a table can have the current timestamp as the default value for initializing the column, as the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.

MYSQL 5.6

Previously, at most one TIMESTAMP column per table could be automatically initialized or updated to the current date and time. This restriction has been lifted. Any TIMESTAMP column definition can have any combination of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses. In addition, these clauses now can be used with DATETIME column definitions. For more information, see Automatic Initialization and Updating for TIMESTAMP and DATETIME.

解决的办法就是：一个不要设置两个默认的时间戳，但是对于大部分人来说肯定没办法满足程序的需求，第二个就是，升级mysql，就5.5的版本升级为5.6

升级的命令为：

## Check current Mysql version
mysql --version

sudo apt-get update        ## Fetch the list of avaliable updated
sudo apt-get upgrade       ## Upgrade the current packages
sudo apt-get dist-upgrade  ## Install new updates.

## Take Mysql databases backup in case you need to restore databases after upgrade,
## Although Mysql automatically restores all the data.

mysqldump --lock-all-tables -u root -p --all-databases > mysql_backup.sql

## Now upgrade the mysql to 5.6
sudo apt-get install mysql-server-5.6

## Restore database if required
mysql -u root -p < mysql_backup.sql

## Check current Mysql version
mysql --version

未经允许不得转载：微信信息发源地 » Incorrect table definition there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause