本节的以下主题提供了DDL操作的在线支持详细信息、语法示例和使用说明。
下表概述了对索引操作的在线DDL支持。星号表示附加信息、异常或依赖关系。有关详细信息,请参见语法和用法注意事项.
表14.10在线DDL支持索引操作
操作 | 在适当的位置 | 重建表 | 允许并发DML | 只修改元数据 |
---|---|---|---|---|
创建或添加二级索引 | 是的 | 没有 | 是的 | 没有 |
删除一个索引 | 是的 | 没有 | 是的 | 是的 |
重命名一个索引 | 是的 | 没有 | 是的 | 是的 |
添加一个全文 指数 |
是的* | 没有* | 没有 | 没有 |
添加一个空间 指数 |
是的 | 没有 | 没有 | 没有 |
更改索引类型 | 是的 | 没有 | 是的 | 是的 |
语法和用法注意事项
创建或添加二级索引
创建索引的名字在表格(col_list);
ALTER TABLEtbl_name添加索引的名字(col_list);
在创建索引时,表仍可用于读写操作。的
创建索引
语句只有在所有访问表的事务都完成后才结束,因此索引的初始状态反映表的最新内容。在线DDL支持添加辅助索引,这意味着通常可以加快创建和加载表及相关索引的整个过程,方法是创建不带辅助索引的表,然后在加载数据后添加辅助索引。
新创建的二级索引只包含表中提交的数据
创建索引
或ALTER TABLE
语句执行完成。它不包含任何未提交的值、值的旧版本或标记为删除但尚未从旧索引中删除的值。如果服务器在创建二级索引时退出,在恢复时,MySQL将删除所有部分创建的索引。你必须重新运行
ALTER TABLE
或创建索引
声明。某些因素会影响该操作的性能、空间使用和语义。有关详细信息,请参见第14.13.6节,“在线DDL限制”.
删除一个索引
指数下降的名字在表格;
ALTER TABLEtbl_name指数下降的名字;
在删除索引时,表仍可用于读写操作。的
指数下降
语句只有在所有访问表的事务都完成后才结束,因此索引的初始状态反映表的最新内容。重命名一个索引
ALTER TABLEtbl_name重命名索引old_index_name来new_index_name算法=原地,锁=没有;
添加一个
全文
指数创建全文索引的名字在表(列);
添加第一个
全文
如果没有用户定义的索引,则重新构建表FTS_DOC_ID
列。额外的全文
可以在不重建表的情况下添加索引。添加一个
空间
指数CREATE TABLE geom (g GEOMETRY NOT NULL);增加空间索引(g),算法=INPLACE,锁=SHARED;
更改索引类型(
使用{btree | hash}
)ALTER TABLEtbl_name删除索引i1,添加索引i1(key_part,……)使用btree,算法= inplace;
下表概述了对主键操作的在线DDL支持。星号表示附加信息、异常或依赖关系。看到语法和用法注意事项.
表14.11 DDL对主键操作的在线支持
操作 | 在适当的位置 | 重建表 | 允许并发DML | 只修改元数据 |
---|---|---|---|---|
添加主键 | 是的* | 是的* | 是的 | 没有 |
删除主键 | 没有 | 是的 | 没有 | 没有 |
删除一个主键并添加另一个主键 | 是的 | 是的 | 是的 | 没有 |
语法和用法注意事项
添加主键
ALTER TABLEtbl_name添加主键(列),算法=原地,锁=没有;
在适当的位置重新构建表。数据被大量地重新组织,这使得它成为一项昂贵的操作。
算法=原地
是否在某些条件下不允许将列转换为非空
.重组聚集索引总是需要复制表数据。因此,最好定义主键当您创建表时,而不是发出
ALTER TABLE……添加主键
以后。当您创建
独特的
或主键
索引,MySQL必须做一些额外的工作。为独特的
索引时,MySQL检查表是否包含键的重复值。对于一个主键
索引,MySQL也检查没有主键
列包含一个零
.方法添加主键时
算法=复制
条款,MySQL转换零
将关联列中的值设置为默认值:0表示数字,空字符串表示基于字符的列和blob, 0000-00-00 00:00:00表示DATETIME
.这是Oracle建议您不要依赖的非标准行为。使用添加主键算法=原地
只允许在SQL_MODE
设置包括strict_trans_tables
或strict_all_tables
国旗;当SQL_MODE
设置是严格的,算法=原地
是允许的,但是如果请求的主键列包含零
值。的算法=原地
行为更加符合标准。如果创建一个没有主键的表,
InnoDB
为你选一个,可以是第一个独特的
上定义的关键非空
列或系统生成的键。要避免不确定性和额外隐藏列的潜在空间需求,请指定主键
条款的一部分创建表
声明。MySQL通过将原始表中的现有数据复制到具有所需索引结构的临时表中来创建一个新的集群索引。将数据完全复制到临时表之后,将使用不同的临时表名重命名原始表。包含新聚集索引的临时表被重命名为原始表的名称,原始表从数据库中删除。
适用于二级索引操作的在线性能增强不适用于主键索引。InnoDB表的行存储在聚集索引根据主键,形成一些数据库系统所称的”index-organized表”.因为表结构与主键紧密相连,所以重新定义主键仍然需要复制数据。
当主键上的操作使用
算法=原地
,即使数据仍然被复制,但它比使用更有效算法=复制
因为:不需要撤消日志记录或相关的重做日志记录
算法=原地
.这些操作增加了DDL语句的开销算法=复制
.二级索引条目是预先排序的,因此可以按顺序加载。
没有使用更改缓冲区,因为辅助索引中没有随机访问插入。
如果服务器在创建新的集群索引时退出,则不会丢失任何数据,但您必须使用该过程中存在的临时表完成恢复过程。由于很少在大表上重新创建聚集索引或重新定义主键,或者在此操作期间遇到系统崩溃,因此本手册不提供如何从这种场景中恢复的信息。
删除主键
ALTER TABLEtbl_name删除主键,算法=拷贝;
只有
算法=复制
支持删除一个主键而不添加一个新的主键ALTER TABLE
声明。删除一个主键并添加另一个主键
ALTER TABLEtbl_name删除主键,添加主键(列),算法=原地,锁=没有;
数据被大量地重新组织,这使得它成为一项昂贵的操作。
下表概述了对列操作的在线DDL支持。星号表示附加信息、异常或依赖关系。有关详细信息,请参见语法和用法注意事项.
表14.12在线DDL对列操作的支持
操作 | 在适当的位置 | 重建表 | 允许并发DML | 只修改元数据 |
---|---|---|---|---|
添加一个列 | 是的 | 是的 | 是的* | 没有 |
删除列 | 是的 | 是的 | 是的 | 没有 |
重命名一个列 | 是的 | 没有 | 是的* | 是的 |
重新排序的列 | 是的 | 是的 | 是的 | 没有 |
设置列默认值 | 是的 | 没有 | 是的 | 是的 |
更改列数据类型 | 没有 | 是的 | 没有 | 没有 |
扩展VARCHAR 列的大小 |
是的 | 没有 | 是的 | 是的 |
删除列默认值 | 是的 | 没有 | 是的 | 是的 |
更改自动递增值 | 是的 | 没有 | 是的 | 没有* |
制作一个列零 |
是的 | 是的* | 是的 | 没有 |
制作一个列非空 |
是的* | 是的* | 是的 | 没有 |
修改对象的定义枚举 或集 列 |
是的 | 没有 | 是的 | 是的 |
语法和用法注意事项
添加一个列
ALTER TABLEtbl_name添加一列column_namecolumn_definition算法=原地,锁=没有;
添加对象时不允许并发DML自动递增列。数据被大量地重新组织,这使得它成为一项昂贵的操作。至少,
算法=原地,锁=共享
是必需的。删除列
ALTER TABLEtbl_name删除列column_name算法=原地,锁=没有;
数据被大量地重新组织,这使得它成为一项昂贵的操作。
重命名一个列
ALTER TABLE资源描述改变old_col_namenew_col_namedata_type算法=原地,锁=没有;
若要允许并发DML,请保持相同的数据类型,只更改列名。
当您保持相同的数据类型和
(不)零
属性,只需更改列名,该操作始终可以在线执行。还可以重命名作为外键约束一部分的列。外键定义将自动更新以使用新的列名。重命名参与外键的列只适用于
算法=原地
.如果您使用算法=复制
子句,或其他一些条件导致操作被使用算法=复制
,ALTER TABLE
语句失败。算法=原地
不支持重命名生成的列.重新排序的列
要重新排列列,请使用
第一个
或后
在改变
或修改
操作。ALTER TABLEtbl_name修改列col_namecolumn_definition首先,算法=原地,锁=没有;
数据被大量地重新组织,这使得它成为一项昂贵的操作。
更改列数据类型
ALTER TABLEtbl_nameCHANGE c1 c1 BIGINT, ALGORITHM=COPY;
只支持更改列数据类型
算法=复制
.扩展
VARCHAR
列的大小ALTER TABLEtbl_nameCHANGE COLUMN c1 VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE;
所需的长度字节数
VARCHAR
列必须保持不变。为VARCHAR
大小为0到255字节的列,需要一个长度字节来对值进行编码。为VARCHAR
大小为256字节或更多的列,需要两个长度字节。结果,到位ALTER TABLE
只支持增加VARCHAR
列大小从0到255字节,或从256字节到更大的大小。就地ALTER TABLE
不支持增加的大小VARCHAR
列从小于256字节到大于或等于256字节。在这种情况下,所需长度字节的数量从1变为2,这仅由表副本(算法=复制
).例如,尝试改变VARCHAR
使用in-place从VARCHAR(255)到VARCHAR(256)的单字节字符集的列大小ALTER TABLE
返回这个错误:ALTER TABLEtbl_name算法=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256);不支持ERROR 0A000: ALGORITHM=INPLACE。原因:无法更改INPLACE列类型。试算法=复制。
请注意a的字节长度
VARCHAR
列的值取决于字符集的字节长度。减少
VARCHAR
大小使用就地ALTER TABLE
不支持。减少VARCHAR
Size需要表副本(算法=复制
).设置列默认值
ALTER TABLEtbl_name改变列上校设置默认文字算法=原地,锁=没有;
删除列默认值
ALTER TABLE资源描述改变列上校删除默认值,算法= inplace,锁= none;
更改自动递增值
ALTER TABLE表格AUTO_INCREMENT =next_value算法=原地,锁=没有;
修改存储在内存中的值,而不是数据文件。
在使用复制或分片的分布式系统中,有时会将表的自动递增计数器重置为特定值。插入到表中的下一行对其自动递增列使用指定的值。您还可以在数据仓库环境中使用此技术,在该环境中您定期清空所有表并重新加载它们,并从1开始重新启动自动递增序列。
制作一个列
零
修改表tbl_namecolumn_namedata_type空,算法=原地,锁=没有;
在适当的位置重新构建表。数据被大量地重新组织,这使得它成为一项昂贵的操作。
制作一个列
非空
ALTER TABLEtbl_name修改列column_namedata_type不是null,算法= inplace,锁= none;
在适当的位置重新构建表。
STRICT_ALL_TABLES
或STRICT_TRANS_TABLES
SQL_MODE
是操作成功所必需的。如果列包含NULL值,则操作失败。服务器禁止对可能导致引用完整性丢失的外键列的更改。看到第13.1.8节,“ALTER TABLE语句”.数据被大量地重新组织,这使得它成为一项昂贵的操作。修改对象的定义
枚举
或集
列CREATE TABLE t1 (c1 ENUM('a', 'b', 'c'));ALTER TABLE t1 MODIFY COLUMN c1 ENUM('a', 'b', 'c', 'd'), ALGORITHM=INPLACE, LOCK=NONE;
修改对象的定义
枚举
或集
类中添加新的枚举或集合成员来创建结束的值,只要数据类型的存储大小不变,就可以原地执行有效成员值列表的。例如,将成员添加到集
有8个成员的列将每个值所需的存储空间从1字节更改为2字节;这需要一个表副本。在列表中间添加成员会导致对现有成员重新编号,这需要表副本。
下表概述了对生成列操作的在线DDL支持。有关详细信息,请参见语法和用法注意事项.
表14.13对生成列操作的在线DDL支持
操作 | 在适当的位置 | 重建表 | 允许并发DML | 只修改元数据 |
---|---|---|---|---|
添加一个存储 列 |
没有 | 是的 | 没有 | 没有 |
修改存储 列顺序 |
没有 | 是的 | 没有 | 没有 |
放弃一个存储 列 |
是的 | 是的 | 是的 | 没有 |
添加一个虚拟 列 |
是的 | 没有 | 是的 | 是的 |
修改虚拟 列顺序 |
没有 | 是的 | 没有 | 没有 |
放弃一个虚拟 列 |
是的 | 没有 | 是的 | 是的 |
语法和用法注意事项
添加一个
存储
列ALTER TABLE t1 ADD COLUMN (c2 INT GENERATED ALWAYS AS (c1 + 1) STORED),算法=COPY;
添加一列
不是存储列的就地操作(在不使用临时表的情况下完成),因为必须由服务器计算表达式。修改
存储
列顺序修改表1中列c2的INT created ALWAYS AS (c1 + 1) STORED FIRST, ALGORITHM=COPY;
在适当的位置重新构建表。
放弃一个
存储
列ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INPLACE, LOCK=NONE
在适当的位置重新构建表。
添加一个
虚拟
列ALTER TABLE t1 ADD COLUMN (c2 INT GENERATED ALWAYS AS (c1 + 1) VIRTUAL), ALGORITHM=INPLACE, LOCK=NONE;
添加虚拟列是针对未分区表的就地操作。但是,添加虚拟列不能与其他虚拟列组合使用
ALTER TABLE
行动。添加一个
虚拟
不是分区表的就地操作。修改
虚拟
列顺序修改表1中的COLUMN c2 INT created ALWAYS AS (c1 + 1)
放弃一个
虚拟
列ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INPLACE, LOCK=NONE
放弃一个
虚拟
列是用于非分区表的就地操作。但是,删除虚拟列不能与其他列组合ALTER TABLE
行动。放弃一个
虚拟
不是分区表的就地操作。
下表概述了对外键操作的在线DDL支持。星号表示附加信息、异常或依赖关系。有关详细信息,请参见语法和用法注意事项.
语法和用法注意事项
添加外键约束
的
原地
算法在以下情况下支持foreign_key_checks
是禁用的。否则,只有复制
算法的支持。ALTER TABLEtbl1添加约束fk_name外键指数(col1)引用tbl2(col2)referential_actions;
删除外键约束
ALTER TABLE资源描述删除外键fk_name;
控件可以在线执行删除外键
foreign_key_checks
选项已启用或禁用。如果不知道特定表上外键约束的名称,则发出以下语句并在
约束
子句用于每个外键:显示创建表表格\ G
或者,查询
INFORMATION_SCHEMA。TABLE_CONSTRAINTS
表,并使用CONSTRAINT_NAME
而且CONSTRAINT_TYPE
列来标识外键名。你也可以在一条语句中删除一个外键和它的关联索引:
ALTER TABLE表格删除外键约束,下降指数指数;
如果外键已经出现在被修改的表中(也就是说,它是子表包含一个外键…参考
子句),附加的限制适用于在线DDL操作,甚至那些不直接涉及外键列的操作:
一个
ALTER TABLE
如果对父表的更改导致子表通过在更新
或在删除
使用条款级联
或设置为空
参数。以同样的方式,如果表是父表在外键关系中,即使它不包含任何
外键
条款,它可以等待ALTER TABLE
若要完成插入
,更新
,或删除
声明原因在更新
或在删除
子表中的操作。
下表概述了对表操作的在线DDL支持。星号表示附加信息、异常或依赖关系。有关详细信息,请参见语法和用法注意事项.
表14.15在线DDL支持表操作
操作 | 在适当的位置 | 重建表 | 允许并发DML | 只修改元数据 |
---|---|---|---|---|
改变了ROW_FORMAT |
是的 | 是的 | 是的 | 没有 |
改变了KEY_BLOCK_SIZE |
是的 | 是的 | 是的 | 没有 |
设置持久表统计信息 | 是的 | 没有 | 是的 | 是的 |
指定字符集 | 是的 | 是的* | 没有 | 没有 |
转换字符集 | 没有 | 是的* | 没有 | 没有 |
优化一个表 | 是的* | 是的 | 是的 | 没有 |
重建的力 选项 |
是的* | 是的 | 是的 | 没有 |
执行空重新构建 | 是的* | 是的 | 是的 | 没有 |
重命名表 | 是的 | 没有 | 是的 | 是的 |
语法和用法注意事项
改变了
ROW_FORMAT
ALTER TABLEtbl_nameROW_FORMAT =row_format算法=原地,锁=没有;
数据被大量地重新组织,这使得它成为一项昂贵的操作。
有关的更多信息
ROW_FORMAT
选项,看到表选项.改变了
KEY_BLOCK_SIZE
ALTER TABLEtbl_nameKEY_BLOCK_SIZE =价值算法=原地,锁=没有;
数据被大量地重新组织,这使得它成为一项昂贵的操作。
有关的更多信息
KEY_BLOCK_SIZE
选项,看到表选项.设置持久表统计选项
ALTER TABLEtbl_nameStats_persistent =0, stats_sample_pages =20, stats_auto_recalc =1, algorithm = inplace, lock = none;
只修改表元数据。
持续的统计数据包括
STATS_PERSISTENT
,STATS_AUTO_RECALC
,STATS_SAMPLE_PAGES
.有关更多信息,请参见第14.8.11.1节“配置持久优化器统计参数”.指定字符集
ALTER TABLEtbl_name字符集=charset_name算法=原地,锁=没有;
如果新的字符编码不同,则重新构建表。
转换字符集
ALTER TABLEtbl_name转换为字符集charset_name,算法=复制;
如果新的字符编码不同,则重新构建表。
优化一个表
优化表tbl_name;
的表不支持就地操作
全文
索引。操作使用原地
算法,但算法
而且锁
不允许使用语法。方法重新构建表
力
选项ALTER TABLEtbl_name力,算法=原地,锁=没有;
使用
算法=原地
截至MySQL 5.6.17.
算法=原地
的表不支持全文
索引。执行“空”重新构建
ALTER TABLEtbl_name引擎= InnoDB算法=原地,锁=没有;
使用
算法=原地
截至MySQL 5.6.17。算法=原地
的表不支持全文
索引。重命名表
ALTER TABLEold_tbl_name重命名为new_tbl_name算法=原地,锁=没有;
MySQL重命名与表对应的文件
tbl_name
不需要复印。(你也可以使用重命名表
语句来重命名表。看到第13.1.33节," RENAME TABLE语句")。专门为重命名表授予的特权不会迁移到新名称。它们必须手动更改。
下表概述了对表空间操作的在线DDL支持。有关详细信息,请参见语法和用法注意事项.
语法和用法注意事项
启用或禁用每表文件表空间加密
ALTER TABLEtbl_name加密= ' Y ',算法=复制;
加密只支持每个表文件的表空间。相关信息请参见第14.14节“InnoDB数据静止加密”.
除了大多数ALTER TABLE
分区子句,用于分区的在线DDL操作InnoDB
表遵循与正则表相同的规则InnoDB
表。
大多数ALTER TABLE
分区子句不通过与常规非分区子句相同的内部在线DDL APIInnoDB
表。因此,在线支持ALTER TABLE
不同分区的条款。
下表显示了它们的在线状态ALTER TABLE
分区的声明。不管使用的是在线DDL API, MySQL都尽量减少数据复制和锁定。
ALTER TABLE
使用算法=复制
或者只允许”算法=默认锁=违约
”,对表重新分区复制
算法。换句话说,用新的分区方案创建了一个新的分区表。控件应用的所有更改都包含在新创建的表中ALTER TABLE
语句,并将表数据复制到新的表结构中。
表14.17在线DDL对分区操作的支持
分区条款 | 在适当的位置 | 允许DML | 笔记 |
---|---|---|---|
分区的 |
没有 | 没有 | 许可证算法=复制 ,锁={默认|分享|专属} |
添加分区 |
没有 | 没有 | 只允许算法=违约 ,锁=违约 .是否为分区的表复制现有数据范围 或列表 .分区的表允许并发查询哈希 或列表 .MySQL在持有共享锁时复制数据。 |
删除分区 |
没有 | 没有 | 只允许算法=违约 ,锁=违约 .是否为分区的表复制现有数据范围 或列表 . |
丢弃分区 |
没有 | 没有 | 只允许算法=违约 ,锁=违约 |
进口分区 |
没有 | 没有 | 只允许算法=违约 ,锁=违约 |
截断分区 |
是的 | 是的 | 不复制现有数据。它只是删除行;它不会改变表本身或其任何分区的定义。 |
合并分区 |
没有 | 没有 | 只允许算法=违约 ,锁=违约 .分区的表允许并发查询哈希 或列表 ,因为MySQL在保持共享锁的同时复制数据。 |
重组分区 |
没有 | 没有 | 只允许算法=违约 ,锁=违约 .分区的表允许并发查询线性散列 或列表 .MySQL在保持共享元数据锁的同时从受影响的分区复制数据。 |
交换分区 |
是的 | 是的 | |
分析分区 |
是的 | 是的 | |
检查分区 |
是的 | 是的 | |
优化分区 |
没有 | 没有 | 算法 而且锁 条款将被忽略。重新构建整个表。看到第22.3.4节“分区的维修”. |
重建分区 |
没有 | 没有 | 只允许算法=违约 ,锁=违约 .分区的表允许并发查询线性散列 或列表 .MySQL在保持共享元数据锁的同时从受影响的分区复制数据。 |
修复分区 |
是的 | 是的 | |
删除分区 |
没有 | 没有 | 许可证算法=复制 ,锁={默认|分享|专属} |
不分区在线ALTER TABLE
对分区表的操作遵循适用于常规表的相同规则。然而,ALTER TABLE
对每个表分区执行在线操作,这会增加对系统资源的需求,因为在多个分区上执行操作。
有关有关ALTER TABLE
分区的条款,请参阅分区选项,第13.1.8.1节“ALTER TABLE Partition Operations”.有关分区的一般信息,请参见22章,分区.