Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to modify data types in MySQL. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Type size range (signed) range (unsigned) usage TINYINT1 byte (- 128127) (0255) small integer value SMALLINT2 byte (- 32 768 minus 32 767) (0meme 65 535) large integer value MEDIUMINT3 byte (- 8 388 608) (0mei 16 777 215) large integer value INT or INTEGER4 byte (- 2 147 483 648 4147 483 647) (0min 4 294 967 295) large integer value BIGINT8 byte (- 9 233 372 036 854 775 808) 9 223 372 036 854 775 807) (0446 744 073 709 551 615) maximum integer value

So now the int data type has reached the maximum value of 2147 483647.

It is generally feasible to modify the data type and expand it, but the MySQL version of this environment is still relatively low, so the tool of pt-osc is forget it, and 2 billion of the data will take a lot of time to process.

After a brief answer to the next question, I have been obsessed with the duration of the operation of modifying the data type.

2 billion of the data to do such an operation, presumably will not experience too many people, but when the friendship support, I logged on to the designated environment, take a closer look, this table originally did not have 2 billion data, but id increased to the level of 2 billion, there are millions of data in the table, the corresponding data file looks at about 500m, so this problem makes my hanging heart finally feel at ease.

# 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

This operation to modify the data type lasted for more than a minute and ended.

The statements provided are as follows:

> 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

Looking at the situation of the thread, you can see that there is a copy to tmp table operation that proves that the table data is being rebuilt in the background.

After the modification is completed, I found that there is something wrong, why there is no attribute of 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)

Use the following way to modify so that the field id is incremented, and an error is thrown.

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

It was this mistake that obsessed me for a long time.

And later continue to try, change the value of auto_increment, but there is no response.

> 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

> 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

This problem is very tangled, the modification is successful, but check the table definition does not take effect, check the incremental sequence value in the data dictionary or NULL, to prove that the self-increasing sequence is not valid.

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

After several attempts, the problem was fixed in the following way.

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

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

> alter table `activity_dj_actor_info_ log` drop primary key

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

Records: 3144627 Duplicates: 0 Warnings: 0

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

Records: 3144627 Duplicates: 0 Warnings: 0

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

`cnMaster` varchar (50) NOT NULL

. . .

PRIMARY KEY (`id`)

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

) ENGINE=InnoDB AUTO_INCREMENT=2150192178 DEFAULT CHARSET=utf8

After a brief communication with the developer's colleagues, it was not long before I looked at it and found that the number was increasing.

Select max (id) from activity_dj_actor_info_log

+-+

| | max (id) |

+-+

| | 2150195418 |

+-+

As for this problem, I also briefly summarized it. In fact, it was not rigorous at the beginning, which led to the continuous repair behind. If one step was in place, there would not be so much trouble.

So there was a simple test locally.

CREATE TABLE `activity_dj_actor_info_ log` (

`id`int (11) NOT NULL AUTO_INCREMENT

`cnMaster` varchar (50) NOT NULL

. . .

PRIMARY KEY (`id`)

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

) ENGINE=InnoDB AUTO_INCREMENT=2147483647 DEFAULT CHARSET=utf8

Insert part of the test data.

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

Query OK, 1000 rows affected (0.07 sec)

Records: 1000 Duplicates: 0 Warnings: 0

Modify table field data type

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

Query OK, 1000 rows affected (.43 sec)

Records: 1000 Duplicates: 0 Warnings: 0

Look at the incremental sequence again and it will be modified and perfected.

> show create table activity_dj_actor_info_log

| | Table | Create Table |

| | activity_dj_actor_info_log | CREATE TABLE `activity_dj_actor_info_ log` (

`id` bigint (20) NOT NULL AUTO_INCREMENT

`cnMaster` varchar (50) NOT NULL

. . .

PRIMARY KEY (`id`)

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

) ENGINE=InnoDB AUTO_INCREMENT=2147483647 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

This is how to modify the data type in the MySQL shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.