MySQL修改資料類型的問題總結

昨天快下班的時候，突然開發的同僚找我說有個緊急需求，負責這個業務的DBA同僚回家了，想讓我幫忙看看，運作個SQL語句，幾秒鐘就好。我一聽，就本着人道主義的精神留下來處理，但是發現似乎留給我的是一個大坑。

了解了問題之後，讓我有些後背發涼，這個表根據開發同僚回報有20億的資料，這得多大的一個表啊，目前的問題是這個表裡的主鍵id資料類型是int,因為資料類型的限制已經達到了最大值，現在插入不了資料了。希望我幫忙處理一下，把資料類型修改為bigint.

我們簡單來了解一下MySQL的資料類型。

對于資料類型有下面的一些總結，更詳細可以參見之前寫的一篇。http://blog.itpub.net/23718752/viewspace-1371434/

類型

大小

範圍（有符号）

範圍（無符号）

用途

TINYINT

1 位元組

(-128，127)

(0，255)

小整數值

SMALLINT

2 位元組

(-32 768，32 767)

(0，65 535)

大整數值

MEDIUMINT

3 位元組

(-8 388 608，8 388 607)

(0，16 777 215)

INT或INTEGER

4 位元組

(-2 147 483 648，2 147 483 647)

(0，4 294 967 295)

BIGINT

8 位元組

(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)

(0，18 446 744 073 709 551 615)

極大整數值

是以現在的int資料類型已經達到了最大值2 147 483 647。

修改資料類型，擴充一般是可行的，但是這個環境MySQL版本還比較低，是以pt-osc的工具是别想了，而且20億的資料就算處理也得耗上不少的時間。

簡答了解了下問題，我一直糾結這個修改資料類型的操作影響時長。

20億的資料做這樣的操作，想必經曆的人也不會太多，偏偏當了友情支援，我登入到指定的環境，仔細一看，這個表原來沒有20億的資料，隻是id遞增到了20億的級别，表裡有幾百萬的資料，對應的資料檔案看有500M左右，是以這個問題讓我懸着的心終于踏實了一些。

# ll -h activity_dj_actor_info_log*

-rw-rw---- 1 mysql mysql 8.7K Sep 29  2014 activity_dj_actor_info_log.frm

-rw-rw---- 1 mysql mysql 560M Nov  4 19:05 activity_dj_actor_info_log.ibd

這個修改資料類型的操作持續了大概1分多鐘就結束了。

提供的語句如下：

> ALTER TABLE activity_dj_actor_info_log modify id  BIGINT;

Query OK, 3144626 rows affected (1 min 22.64 sec)

Records: 3144626  Duplicates: 0  Warnings: 0

檢視線程的情況，可以看到存在這麼一個copy to tmp table的操作，證明在背景重建表資料。

修改完成之後檢視，發現有個地方不對勁，怎麼沒有了auto_increment的屬性。

> show create table activity_dj_actor_info_log\G

*************************** 1. row ***************************

       Table: activity_dj_actor_info_log

Create Table: CREATE TABLE `activity_dj_actor_info_log` (

  `id` bigint(20) NOT NULL DEFAULT '0',

  `cnMaster` varchar(50) NOT NULL,

。。。

  PRIMARY KEY (`id`),

  UNIQUE KEY `dss_cnMaster` (`cnMaster`,`serverId`,`guid`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

使用下面的方式修改，讓字段id遞增，竟然抛出了錯誤。

> ALTER TABLE activity_dj_actor_info_log modify id  BIGINT AUTO_INCREMENT;

ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'

就是這個錯誤讓我糾結了半天。

而且稍後繼續嘗試，修改auto_increment的值，竟然沒有反應。

> ALTER TABLE activity_dj_actor_info_log AUTO_INCREMENT=2147483649;

Query OK, 3144627 rows affected (1 min 20.65 sec)

Records: 3144627  Duplicates: 0  Warnings: 0

 。。。

這問題就很糾結了，修改成功，但是檢視表定義沒有生效，檢視資料字典裡的遞增序列值還是NULL，證明自增序列沒有生效。

> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name="activity_dj_actor_info_log";

+----------------+

| AUTO_INCREMENT |

|           NULL |

2 rows in set (0.00 sec)

在經過幾次嘗試之後，最後是采用下面的方式才修複了這個問題。

> alter table `activity_dj_actor_info_log` change `id` `id` bigint  NOT NULL AUTO_INCREMENT , drop primary key,add primary key(id);

> alter table `activity_dj_actor_info_log`  drop primary key;

Query OK, 3144627 rows affected (1 min 13.75 sec)

> alter table `activity_dj_actor_info_log` change `id` `id` bigint  NOT NULL AUTO_INCREMENT , add primary key(id);

Query OK, 3144627 rows affected (1 min 32.32 sec)

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

  。。。

) ENGINE=InnoDB AUTO_INCREMENT=2150192178 DEFAULT CHARSET=utf8

和開發的同僚簡單溝通之後，沒過一會檢視就發現數值是遞增了。

> select max(id) from activity_dj_actor_info_log;

+------------+

| max(id)    |

| 2150195418 |

而對于這個問題，自己也簡單總結了下，其實最開始處理的時候就不嚴謹，導緻了後面的不斷修複，如果一步到位就不會有這麼多的麻煩了。

是以在本地有簡單測試了下。

CREATE TABLE `activity_dj_actor_info_log` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

) ENGINE=InnoDB AUTO_INCREMENT=2147483647 DEFAULT CHARSET=utf8;

插入一部分測試資料。

> insert into activity_dj_actor_info_log select *from activity_log.activity_dj_actor_info_log limit 1,1000;

Query OK, 1000 rows affected (0.07 sec)

Records: 1000  Duplicates: 0  Warnings: 0

修改表字段資料類型

> alter table activity_dj_actor_info_log modify  `id` bigint  NOT NULL AUTO_INCREMENT;

Query OK, 1000 rows affected (0.43 sec)

再次檢視遞增序列就修改完善了。

> show create table activity_dj_actor_info_log;

| Table                      | Create Table      

| activity_dj_actor_info_log | CREATE TABLE `activity_dj_actor_info_log` (

) ENGINE=InnoDB AUTO_INCREMENT=2147483647 DEFAULT CHARSET=utf8

在這一點上，Oracle的處理和MySQL還是存在一些差別，還是需要嚴格差別對待。