linux - Mysql datetime DEFAULT CURRENT_TIMESTAMP error

Question

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?

Answer 1

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.