mysql - 将表 INT 值从有符号执行更改为无符号执行

Question

我有一个表，其主键为最大签名 INT 命中，2147483647

想象一下，我想将它切换为无符号的，并且表中没有负值，因为它是主键，因为我目前认为这是让表重新运行的最快方法。

ALTER TABLE 语句将其切换为无符号 INT 是否应该是一个相对快速的过程，因为 ID 的值不应更改？锁定呢？

Answer 1

关于 ALTER TABLE 的 Mysql 文档命令在“存储、性能和并发注意事项”部分下非常详细地描述了哪些更改可以快速完成，无需表复制和索引重建，以及在命令过程中 mysql 将应用哪些锁。不幸的是，更改列类型没有列为可以就地完成的事情(当然，请阅读与您的 mysql 版本对应的文档，我只是链接了最新的)。

For some operations, an in-place ALTER TABLE is possible that does not require a temporary table:

For ALTER TABLE tbl_name RENAME TO new_tbl_name without any other options, MySQL simply renames any files that correspond to the table tbl_name without making a copy. (You can also use the RENAME TABLE statement to rename tables. See Section 13.1.28, “RENAME TABLE Syntax”.) Any privileges granted specifically for the renamed table are not migrated to the new name. They must be changed manually.

Alterations that modify only table metadata and not table data are immediate because the server only needs to alter the table .frm file, not touch table contents. The following changes are fast alterations that can be made this way:

• Renaming a column.

• Changing the default value of a column.

• Changing the definition of an ENUM or SET column by adding new enumeration or set members to the end of the list of valid member values, as long as the storage size of the data type does not change. For example, adding a member to a SET column that has 8 members changes the required storage per value from 1 byte to 2 bytes; this will require a table copy. Adding members in the middle of the list causes renumbering of existing members, which requires a table copy.

• ALTER TABLE with DISCARD ... PARTITION ... TABLESPACE or IMPORT ... PARTITION ... TABLESPACE do not create any temporary tables or temporary partition files.

• ALTER TABLE with ADD PARTITION, DROP PARTITION, COALESCE PARTITION, REBUILD PARTITION, or REORGANIZE PARTITION does not create any temporary tables (except when used with NDB tables); however, these operations can and do create temporary partition files.

• ADD or DROP operations for RANGE or LIST partitions are immediate operations or nearly so. ADD or COALESCE operations for HASH or KEY partitions copy data between all partitions, unless LINEAR HASH or LINEAR KEY was used; this is effectively the same as creating a new table, although the ADD or COALESCE operation is performed partition by partition. REORGANIZE operations copy only changed partitions and do not touch unchanged ones.

• Renaming an index.

• Adding or dropping an index, for InnoDB.

锁定:

While ALTER TABLE is executing, the original table is readable by other sessions (with the exception noted shortly). Updates and writes to the table that begin after the ALTER TABLE operation begins are stalled until the new table is ready, then are automatically redirected to the new table without any failed updates. The temporary copy of the original table is created in the database directory of the new table. This can differ from the database directory of the original table for ALTER TABLE operations that rename the table to a different database.

The exception referred to earlier is that ALTER TABLE blocks reads (not just writes) at the point where it is ready to install a new version of the table .frm file, discard the old file, and clear outdated table structures from the table and table definition caches. At this point, it must acquire an exclusive lock. To do so, it waits for current readers to finish, and blocks new reads (and writes).