在 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>

看起來它有效。如果您不信任這個小樣本，則製作該表的副本並使用該副本執行此操作。

試一試 ！！！

引用自：https://dba.stackexchange.com/questions/34405