tidb 修改表字段类型

tidb尚未支持“有损更改”，实验一些，总结一下常用的，同一类型小转大可以，大转小不行，例如：int(2)转int(4)。不同类型不能转换，也有例外int(2)转bigint(2)。

mysql> CREATE TABLE `tank` (
    ->   `id` int(4) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
    ->   `ids` varchar(10) NOT NULL DEFAULT 0 COMMENT 'test',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 COMMENT='test';
Query OK, 0 rows affected (0.75 sec)

mysql> desc tank;//ids是varchar(10)
+---------+-----------------+------+------+---------+----------------+
| Field   | Type            | Null | Key  | Default | Extra          |
+---------+-----------------+------+------+---------+----------------+
| id      | int(4) unsigned | NO   | PRI  | NULL    | auto_increment |
| ids     | varchar(10)     | NO   |      | 0       |                |
+---------+-----------------+------+------+---------+----------------+
2 rows in set (0.00 sec)

//改表字段报错
mysql> ALTER TABLE `tank` CHANGE `ids` `ids` int(6) NOT NULL DEFAULT '0' COMMENT 'test';
ERROR 8200 (HY000): Unsupported modify column: type int(6) not match origin varchar(10)

mysql> insert into tank(ids)value(1);
Query OK, 1 row affected (0.05 sec)

mysql> select * from tank;
+----+-----+
| id | ids |
+----+-----+
|  1 | 1   |
+----+-----+
1 row in set (0.01 sec)

//新增一个字段
mysql> ALTER TABLE `tank` ADD `ids_bak` INT(6) NOT NULL DEFAULT '0' AFTER `ids`;
Query OK, 0 rows affected (0.44 sec)

mysql> desc tank;
+---------+-----------------+------+------+---------+----------------+
| Field   | Type            | Null | Key  | Default | Extra          |
+---------+-----------------+------+------+---------+----------------+
| id      | int(4) unsigned | NO   | PRI  | NULL    | auto_increment |
| ids     | varchar(10)     | NO   |      | 0       |                |
| ids_bak | int(6)          | NO   |      | 0       |                |
+---------+-----------------+------+------+---------+----------------+
3 rows in set (0.00 sec)

mysql> update tank set ids_bak=ids;  //将ids的数据更新到ids_bak
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tank;
+----+-----+---------+
| id | ids | ids_bak |
+----+-----+---------+
|  1 | 1   |       1 |
+----+-----+---------+
1 row in set (0.00 sec)

mysql> ALTER TABLE `tank` drop ids; //删除ids
Query OK, 0 rows affected (0.34 sec)

mysql> select * from tank;
+----+---------+
| id | ids_bak |
+----+---------+
|  1 |       1 |
+----+---------+
1 row in set (0.00 sec)

mysql> ALTER TABLE `tank` change ids_bak ids INT(6); //将ids_bak改名字为ids
Query OK, 0 rows affected (0.11 sec)

mysql> desc tank;//字段类型已修改
+-------+-----------------+------+------+---------+----------------+
| Field | Type            | Null | Key  | Default | Extra          |
+-------+-----------------+------+------+---------+----------------+
| id    | int(4) unsigned | NO   | PRI  | NULL    | auto_increment |
| ids   | int(6)          | YES  |      | NULL    |                |
+-------+-----------------+------+------+---------+----------------+
2 rows in set (0.00 sec)

mysql> select * from tank;
+----+------+
| id | ids  |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.00 sec)

mysql> exit