Mysql
在 MySQL 中將列類型 DATETIME 更改為 TIMESTAMP 時會發生什麼?
我想在超過一百萬行的表中將列數據類型從 DATETIME 轉換為 TIMESTAMP。
可能嗎?慢嗎?我會失去舊資訊還是 MySQL 可以自動轉換?我可以只做一個改變表還是可以做一些不同的事情?
這是一個測試腳本:
use test drop table if exists felipe_table; create table felipe_table ( id int not null auto_increment, dt datetime not null, name varchar(25), primary key (id) ); insert into felipe_table (dt,name) values (NOW() + INTERVAL FLOOR(RAND()*10) DAY,'rolando'), (NOW() + INTERVAL FLOOR(RAND()*100) DAY,'pamela'), (NOW() + INTERVAL FLOOR(RAND()*1000) DAY,'dominique'), (NOW() + INTERVAL FLOOR(RAND()*10000) DAY,'diamond'); select * from felipe_table; SHOW CREATE TABLE felipe_table\G ALTER TABLE felipe_table MODIFY dt TIMESTAMP; select * from felipe_table; SHOW CREATE TABLE felipe_table\G
這是它的執行(使用 MySQL 5.5.12-log for Windows):
mysql> use test Database changed mysql> drop table if exists felipe_table; Query OK, 0 rows affected (0.03 sec) mysql> create table felipe_table -> ( -> id int not null auto_increment, -> dt datetime not null, -> name varchar(25), -> primary key (id) -> ); Query OK, 0 rows affected (0.11 sec) mysql> insert into felipe_table (dt,name) values -> (NOW() + INTERVAL FLOOR(RAND()*10) DAY,'rolando'), -> (NOW() + INTERVAL FLOOR(RAND()*100) DAY,'pamela'), -> (NOW() + INTERVAL FLOOR(RAND()*1000) DAY,'dominique'), -> (NOW() + INTERVAL FLOOR(RAND()*10000) DAY,'diamond'); Query OK, 4 rows affected (0.13 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from felipe_table; +----+---------------------+-----------+ | id | dt | name | +----+---------------------+-----------+ | 1 | 2013-02-16 09:01:21 | rolando | | 2 | 2013-02-09 09:01:21 | pamela | | 3 | 2014-09-07 09:01:21 | dominique | | 4 | 2036-03-07 09:01:21 | diamond | +----+---------------------+-----------+ 4 rows in set (0.00 sec) mysql> SHOW CREATE TABLE felipe_table\G *************************** 1. row *************************** Table: felipe_table Create Table: CREATE TABLE `felipe_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `dt` datetime NOT NULL, `name` varchar(25) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> ALTER TABLE felipe_table MODIFY dt TIMESTAMP; Query OK, 4 rows affected (0.31 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from felipe_table; +----+---------------------+-----------+ | id | dt | name | +----+---------------------+-----------+ | 1 | 2013-02-16 09:01:21 | rolando | | 2 | 2013-02-09 09:01:21 | pamela | | 3 | 2014-09-07 09:01:21 | dominique | | 4 | 2036-03-07 09:01:21 | diamond | +----+---------------------+-----------+ 4 rows in set (0.00 sec) mysql> SHOW CREATE TABLE felipe_table\G *************************** 1. row *************************** Table: felipe_table Create Table: CREATE TABLE `felipe_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `name` varchar(25) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql>
看起來它有效。如果您不信任這個小樣本,則製作該表的副本並使用該副本執行此操作。
試一試 !!!