mysql date&datetime类型更新问题

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

mysql> desc nongfu.ecs_users; +-----------------+------------------------+------+-----+---------------------+----------------+ | Field           | Type                   | Null | Key | Default             | Extra          | +-----------------+------------------------+------+-----+---------------------+----------------+ | user_id         | mediumint(8) unsigned  | NO   | PRI | NULL                | auto_increment | | email           | varchar(60)            | NO   | MUL |                     |                | | user_name       | varchar(60)            | NO   | UNI |                     |                | | password        | varchar(32)            | NO   |     |                     |                | | question        | varchar(255)           | NO   |     |                     |                | | answer          | varchar(255)           | NO   |     |                     |                | | sex             | tinyint(1) unsigned    | NO   |     | 0                   |                | | birthday        | date                   | NO   |     | 1000-01-01          |                | | user_money      | decimal(10,2)          | NO   |     | 0.00                |                | | frozen_money    | decimal(10,2)          | NO   |     | 0.00                |                | | pay_points      | int(10) unsigned       | NO   |     | 0                   |                | | rank_points     | int(10) unsigned       | NO   |     | 0                   |                | | address_id      | mediumint(8) unsigned  | NO   |     | 0                   |                | | reg_time        | int(10) unsigned       | NO   |     | 0                   |                | | last_login      | int(11) unsigned       | NO   |     | 0                   |                | | last_time       | datetime               | NO   |     | 1000-01-01 00:00:00 |                | | last_ip         | varchar(15)            | NO   |     |                     |                | | visit_count     | smallint(5) unsigned   | NO   |     | 0                   |                | | user_rank       | tinyint(3) unsigned    | NO   |     | 0                   |                | | is_special      | tinyint(3) unsigned    | NO   |     | 0                   |                | | ec_salt         | varchar(10)            | YES  |     | NULL                |                | | salt            | varchar(10)            | NO   |     | 0                   |                | | parent_id       | mediumint(9)           | NO   | MUL | 0                   |                | | flag            | tinyint(3) unsigned    | NO   | MUL | 0                   |                | | alias           | varchar(60)            | NO   |     | NULL                |                | | msn             | varchar(60)            | NO   |     | NULL                |                | | qq              | varchar(20)            | NO   |     | NULL                |                | | office_phone    | varchar(20)            | NO   |     | NULL                |                | | home_phone      | varchar(20)            | NO   |     | NULL                |                | | mobile_phone    | varchar(20)            | NO   |     | NULL                |                | | is_validated    | tinyint(3) unsigned    | NO   |     | 0                   |                | | credit_line     | decimal(10,2) unsigned | NO   |     | NULL                |                | | passwd_question | varchar(50)            | YES  |     | NULL                |                | | passwd_answer   | varchar(255)           | YES  |     | NULL                |                | +-----------------+------------------------+------+-----+---------------------+----------------+ 34 rows in set

执行以下代码错误：

1 2	update nongfu.ecs_users set birthday = '1955-01-01' where birthday = '0000-00-00'; update nongfu.ecs_users set  last_time = '1000-01-01 00:00:00' where last_time = '0000-00-00 00:00:00';

正确的写法：

1 2	update nongfu.ecs_users set birthday = '1955-01-01' where birthday = 0000-00-00; update nongfu.ecs_users set  last_time = '1000-01-01 00:00:00' where last_time = 0000-00-00;

本文转自 tanzhenchao 51CTO博客，原文链接：http://blog.51cto.com/cmdschool/1709479，如需转载请自行联系原作者