Today, we are going to import the latest table into their previous machine to do temporary development, in the database import encountered a problem: incorrect table definition; There can is only one TIMESTAMP column with Current_timestamp in DEFAULT or on UPDATE clause
More imported log hints are: Table error definitions, only one column in a table can be specified as a column of type TIMESTAMP, and use Current_timestamp as the default value or use the on UPDATE clause. I have never met this problem before ... I do not operate the database AH.
Search for most of the versions that are supposed to be my database after 5.6: I saw the version of my local MySQL. It turns out I used 5.5 of the previous one. That's the reason ... As long as you find the reason, there is a way to solve: Open the SQL file to operate, you can see the following: It is true that two columns are defined in a table as timestamp types.
Reprint other people's solutions are as follows:
Note: This article discusses the range of tables that are not enabled for MAXDB mode!
A table was built today with a column that is the timestamp type, and I meant that when the data is updated, the time of this field will be updated automatically. Cruised does not know much about this type of value, causing an error. This field is found to have a value only when the data is set up, but it is not changed in the update.
Find the information, found that I built the statement of the table has a problem:
CREATE TABLE ' Test ' ( timestampnotNULLdefaultcurrent_timestamp , varchar(5notNULL) ENGINE=MyISAM;
In fact, the following two build statements have the same effect:
CREATE TABLE' Test ' (' T1 ' )TIMESTAMP not NULL, ' ww 'VARCHAR(5) not NULL) ENGINE=MYISAMCREATE TABLE' Test ' (' T1 ' )timestamp not NULL default Current_timestamp on Update Current_timestamp, ' ww 'varchar(5) not NULL) ENGINE=MyISAM;
In comparison, my statement is less "on update Current_timestamp" or more "default Current_timestamp". As a result, this timestamp field is only set at the time of the data insert, and the update will not change. Of course, it doesn't matter if you want to achieve that.
Find the English Manual (http://dev.mysql.com/doc/refman/5.1/en/timestamp.html) for a more detailed explanation, translated as follows:
-----------------------------------Translation begins--------------------------------
In the CREATE TABLE statement, the 1th timestamp column can be declared in one of the following ways:
1: If the default Current_timestamp and on UPDATE CURRENT_TIMESTAMP clauses are defined, the value of the column is the current timestamp, and is automatically updated.
2: If the default or on UPDATE clause is not used, it is equivalent to default Current_timestamp on update current_timestamp.
3: If there is only the default CURRENT_TIMESTAMP clause, and there is no on UPDATE clause, the column value defaults to the current timestamp but does not update automatically.
4: If the default clause is not used, but there is an on Update CURRENT_TIMESTAMP clause, the column defaults to 0 and is automatically updated.
5: If there is a constant value of default, the column will have a default value and will not be automatically initialized to the current timestamp. If the column also has an ON Update CURRENT_TIMESTAMP clause, the timestamp is updated automatically, otherwise the column has a default constant but does not update automatically.
In other words, you can use the current timestamp to initialize values and Automatic Updates, or either, or none. (For example, you can specify an automatic update when you define it, but not initialize it.) The following field definitions illustrate these conditions:
automatic initialization and update: TSTIMESTAMP DEFAULT Current_timestamp on UPDATE Current_timestampAutomatic initialization only: TSTIMESTAMP DEFAULT Current_timestampautomatically update TS onlyTIMESTAMP DEFAULT 0 on UPDATE Current_timestampjust give a constant (note:0000-xx-xx xx:xx:xx) TSTIMESTAMP DEFAULT 0
-----------------------------------End of translation--------------------------------
Above I marked "1th timestamp column" In red font, because a table with multiple timestamp columns is defined differently.
For example, the second of the above: "If you do not use the default or ON UPDATE clause, it is equivalent to the default current_timestamp on update current_timestamp." "If the second timestamp column is different, see:
CREATE TABLE VARCHAR9notnullTIMESTAMP not null DEFAULTcurrent_timestampTIMESTAMPnotNULL = MYISAM
In this case, when the data is inserted, T1 will record the current time, T2 as the default (0000-00-00 00:00:00), equivalent to the following statement:
CREATE TABLE' Test ' (' WW 'VARCHAR(9) not NULL, ' t1 'TIMESTAMP not NULL DEFAULT Current_timestamp, ' T2 'TIMESTAMP not NULL DEFAULT '0000-00-00 00:00:00') ENGINE=MYISAM
And according to the second article above, it should be:
CREATE TABLE' Test ' (' WW 'VARCHAR(9) not NULL, ' t1 'TIMESTAMP not NULL DEFAULT Current_timestamp, ' T2 'TIMESTAMP on UPDATE Current_timestamp not NULL DEFAULT Current_timestamp) ENGINE=MYISAM
But in fact, the above statement is a syntax error and MySQL will return:
#1293-tableonlyTIMESTAMPcolumnwithCurrent_ TIMESTAMPinDEFAULToronUPDATE clause
I have wanted to design a table, this table has two timestamp columns, one can record the update time, one can record the initial time, but after trying many times, I found that MySQL does not seem to be able to do this, do not know whether this is a MySQL defect or self-optimization, because, This function can use DateTime to realize the time of the initialization of the record, just need to specify when the insert.
Transferred from: http://blog.sina.com.cn/s/blog_49a665e10100cb52.html
Timestamp type issues encountered with MySQL data import