mysql datetime DEFAULT CURRENT_TIMESTAMP错误 [英] Mysql datetime DEFAULT CURRENT_TIMESTAMP error

问题描述

1. 当我在Windows上运行此MYSQL语法时，它可以正常运行:

CREATE TABLE New
(
  id bigint NOT NULL AUTO_INCREMENT,
  timeUp datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
)

但是当我尝试在Linux上运行此代码时，出现错误:

 #1067 - Invalid default value for 'time'

2. 在Windows上，大小写不敏感，例如. New和new都被认为是相同的.但是在Linux上，这种情况是敏感的.

Linux的配置:

MySQL 5.5.33，phpMyAdmin:4.0.5，PHP:5.2.17

Windows的配置:

MySql:5.6.11，phpMyAdmin:4.0.4.1，PHP:5.5.0

有什么办法可以使它们在两个系统中都通用?还是其他替代方法?

解决方案

MySQL 5.6中添加了对 DATETIME (数据类型)的 DEFAULT CURRENT_TIMESTAMP 支持.

在5.5及更早版本中，这仅适用于 TIMESTAMP (数据类型)列.

可以在5.5中使用 BEFORE INSERT 触发器将默认值分配给列.

 DELIMITER $$

 CREATE TRIGGER ...
 BEFORE INSERT ON mytable
 FOR EACH ROW
 BEGIN
    IF NEW.mycol IS NULL THEN
       SET NEW.mycol = NOW();
    END IF;
 END$$

---

<罢工> (对列中存储的值的查询)区分大小写的原因是该列使用了 collation .以 _ci 结尾的排序规则不区分大小写.例如， latin1_swedish_ci 不区分大小写，而 latin1_general_cs 则区分大小写.

SHOW CREATE TABLE foo 的输出将显示字符集和字符类型列的排序规则.在每个列级别指定.当新列定义未指定字符集时，在表级别指定的默认"适用于添加到表中的新列.

更新

Kaii指出，我对区分大小写"的回答涉及存储在列中的值，并且查询是否将从包含"New"的列中返回一个值，将返回诸如"t.col = 'new'"的谓词. /p>

请参阅Kaii有关在Windows上与Linux上对标识符(例如表名)的处理方式不同(默认情况下)的答案.

1. When I ran this MYSQL syntax on windows it ran properly:

CREATE TABLE New
(
  id bigint NOT NULL AUTO_INCREMENT,
  timeUp datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
)

But when I tried running this code on Linux I got an error:

 #1067 - Invalid default value for 'time'

2. On windows the case is not sensitive eg. New and new both are considered to be same. But on linux the case is sensitive.

Configuration of Linux:

MySQL 5.5.33, phpMyAdmin: 4.0.5, PHP: 5.2.17

Configuration of Windows:

MySql: 5.6.11, phpMyAdmin: 4.0.4.1, PHP: 5.5.0

Is there any way to make them common for both systems? Or any alternative approach?

解决方案

The DEFAULT CURRENT_TIMESTAMP support for a DATETIME (datatype) was added in MySQL 5.6.

In 5.5 and earlier versions, this applied only to TIMESTAMP (datatype) columns.

It is possible to use a BEFORE INSERT trigger in 5.5 to assign a default value to a column.

 DELIMITER $$

 CREATE TRIGGER ...
 BEFORE INSERT ON mytable
 FOR EACH ROW
 BEGIN
    IF NEW.mycol IS NULL THEN
       SET NEW.mycol = NOW();
    END IF;
 END$$

---

Case sensitivity (of queries against values stored in columns) is due to the collation used for the column. Collations ending in _ci are case insensitive. For example latin1_swedish_ci is case insensitive, but latin1_general_cs is case sensitive.

The output from SHOW CREATE TABLE foo will show the character set and collation for the character type columns. This is specified at a per-column level. The "default" specified at the table level applies to new columns added to the table when the new column definition doesn't specify a characterset.

UPDATE

Kaii pointed out that my answer regarding "case sensitivity" deals with values stored within columns, and whether queries will return a value from a column containing a value of "New" will be returned with a predicate like "t.col = 'new'".

See Kaii's answer regarding identifiers (e.g. table names) being handled differently (by default) on Windows than on Linux.

这篇关于mysql datetime DEFAULT CURRENT_TIMESTAMP错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！