mysql alter添加列的实现方式

目录
  • mysql alter添加列
    • alter的执行过程如下
  • mysql基础之alter字段解读
    • 1、先创建一张表testalter_tbl
    • 2、删除，添加或修改表字段
    • 3、修改字段类型及名称
    • 4、 ALTER TABLE 对 Null 值和默认值的影响
  • 总结

mysql alter添加列

当表中的数据量不是很大的时候可以直接使用alter命令添加新列，5.6及其之前的版本会将表锁住。

alter的执行过程如下

1、将原表锁住，不能进行更新、插入、删除等修改操作

2、创建了一个临时表，添加想要的字段

3、将原表中的数据复制到临时表中

4、将临时表改名，并删除原表

在5.6之后的版本mysql数据库做了很多优化，像onlineDDL在执行alter时可以指定algorithm和lock字段，用于选择ddl修改表结构时的算法和是否对原表加锁，algorithm为inplace表示添加字段时不再创建临时表，直接在原表上添加字段，避免重建表带来的IO和cpu消耗；lock = none表示进行ddl时不再锁表，允许进行表数据的修改及访问

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;   

当然如果它的数据量特别巨大，且访问很频繁的话，添加列的速度还是很慢，可以考虑选取一个访问量没那么大的时间段，先在从库添加这一列，成功之后，进行主从切换，然后在原master节点和其余从节点表中添加该字段；

还可以使用pt_ocs工具，它会创建一个临时表（当然临时表结构是按照需要修改完全后的），然后将原表的数据copy到临时表，并且会有一个触发器，用于记录并同步在copy过程中原表的数据变化，在数据同步完之后会短暂的锁表执行触发器，将临时表rename并删除原表。

MT用的是在线DDL工具gh-ost

临时表从主库拉取数据的速度由chunk-size参数控制，默认每次1000条 每秒复制30次 写QPS 3W（若从库临时表根据binlog同步数据写入速度没跟上，当执行rename命令，完成表替换后，有可能会产生主从延迟 ）

mysql基础之alter字段解读

1、先创建一张表testalter_tbl

    create table testalter_tbl
    (
     i INT,
     c CHAR(1)
    );

2、删除，添加或修改表字段

  • 删除字段i
alter table testalter_tbl drop i

注意：如果数据表中只剩余一个字段则无法使用DROP来删除字段。

  • 添加字段i

使用 ADD 子句来向数据表中添加列，如下实例在表 testalter_tbl 中添加 i 字段，并定义数据类型:

ALTER TABLE testalter_tbl ADD i INT;

注意：执行以上命令后，i 字段会自动添加到数据表字段的末尾。

也可以指定添加字段的位置，通过使用使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。

ALTER TABLE testalter_tbl DROP i;#删除i这一列
ALTER TABLE testalter_tbl ADD i INT FIRST;#把i这一列添加到第一列
ALTER TABLE testalter_tbl DROP i;#删除i这一列
ALTER TABLE testalter_tbl ADD i INT AFTER c;#把i这一列添加到c之后

注意：FIRST 和 AFTER 关键字可用于 ADD 与 MODIFY 子句，所以如果你想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。

3、修改字段类型及名称

如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。

  • 把字段 c 的类型从 CHAR(1) 改为 CHAR(10)
ALTER TABLE testalter_tbl MODIFY c CHAR(10);
  • 使用 CHANGE 子句修改
ALTER TABLE testalter_tbl CHANGE i j BIGINT;#可以通过change改变字段名和字段类型

4、 ALTER TABLE 对 Null 值和默认值的影响

当你修改字段时，你可以指定是否包含值或者是否设置默认值。

ALTER TABLE testalter_tbl 
     MODIFY j BIGINT NOT NULL DEFAULT 100;

注意：如果你不设置默认值，MySQL会自动设置该字段默认为 NULL。

  • 修改默认值
ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
  • 删除字段的默认值
ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
  • 修改表名
ALTER TABLE testalter_tbl RENAME TO alter_tbl;

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。

赞 (0)

相关推荐

  • MySQL修改表一次添加多个列(字段)和索引的方法

    MySQL修改表一次添加多个列(字段) ALTER TABLE table_name ADD func varchar(50), ADD gene varchar(50), ADD genedetail varchar(50); MySQL修改表一次添加多个索引 ALTER TABLE  table_name ADD INDEX idx1 ( `func`), ADD INDEX idx2 ( `func`,`gene`), ADD INDEX idx3( `genedetail`); 以上这篇

  • Mysql数据库 ALTER 操作详解

    目录 背景: 案例一:将表employees的lastName字段修改到firstName字段后面,并减少字符长度. 案例二:将表employees的sex字段改名为employee_sex. 案例三:修改employee_sex字段,数据类型为CHAR(1),非空约束 案例四:删除字段employee_sex 案例五:增加字段名city,数据类型为VARCHAR(10). 案例六:将表employees名称修改为employees_info 背景: ALTER作为DDL语言之一,工作中经常遇到

  • MySQL 大表添加一列的实现

    MySQL 大表添加一列的实现

    问题参考自: https://www.zhihu.com/question/440231149 ,mysql中,一张表里有3亿数据,未分表,要求是在这个大表里添加一列数据.数据库不能停,并且还有增删改操作.请问如何操作?答案为个人原创 以前老版本 MySQL 添加一列的方式: ALTER TABLE 你的表 ADD COLUMN 新列 char(128); 会造成锁表,简易过程如下: 新建一个和 Table1 完全同构的 Table2 对表 Table1 加写锁 在表 Table2 上执行 AL

  • mysql alter添加列的实现方式

    目录 mysql alter添加列 alter的执行过程如下 mysql基础之alter字段解读 1.先创建一张表testalter_tbl 2.删除,添加或修改表字段 3.修改字段类型及名称 4. ALTER TABLE 对 Null 值和默认值的影响 总结 mysql alter添加列 当表中的数据量不是很大的时候可以直接使用alter命令添加新列,5.6及其之前的版本会将表锁住. alter的执行过程如下 1.将原表锁住,不能进行更新.插入.删除等修改操作 2.创建了一个临时表,添加想要的

  • MySQL表和列的注释总结

    像代码一样,可以为表以及表中的列添加注释,方便其他人知晓其功能.对于一些字段,在经过一定时间后,创建者未必也能想起其具体的含意,所以注释显得尤为重要. 注释的添加 注释的添加是通过在定义表或列的时候在末尾加上 COMMENT 关键字来实现的,最长支持 1024 个字符. 可以在创建表的时候为表和列添加相应的注释. CREATE TABLE test_comment ( id SERIAL PRIMARY KEY, col1 INT comment '列的注释' ) comment '表的注释';

  • mysql表的四种分区方式总结

    mysql表的四种分区方式总结

    目录 1.什么是表分区? 2.表分区与分表的区别 3.表分区有什么好处? 4.分区表的限制因素 5.如何判断当前MySQL是否支持分区? 6.MySQL支持的分区类型有哪些? 6.1.RANGE分区 6.1.1.根据数值范围 6.1.2.根据TIMESTAMP范围 6.1.3.根据DATE.DATETIME范围 6.1.4.根据多列范围 6.1.5.RANGE分区在如下场合特别有用 6.2.LIST分区 6.3.HASH分区 6.4.KEY分区 总结 1.什么是表分区? mysql数据库中的数据

  • MySQL ALTER命令使用详解

    ALTER TABLE允许你修改一个现有表的结构.例如,你可以增加或删除列.创造或消去索引.改变现有列的类型.或重新命名列或表本身.你也能改变表的注释和表的类型. 你可以使用CHANGE old_col_name create_definition子句重命名一个列.为了这样做,指定旧的和新的列名字和列当前有的类型.例如,重命名一个INTEGER列,从a到b,你可以这样做: mysql> ALTER TABLE t1 CHANGE a b INTEGER; 如果你想要改变列的类型而非名字,就算他们

  • MySQL ALTER命令知识点汇总

    当我们需要修改数据表名或者修改数据表字段时,就需要使用到MySQL ALTER命令. 1.先创建一张表,表名为:testalter_tbl. 2.删除,添加或修改表字段 如下命令使用了 ALTER 命令及 DROP 子句来删除以上创建表的 i 字段: 3.如果数据表中只剩余一个字段则无法使用DROP来删除字段. MySQL 中使用 ADD 子句来向数据表中添加列,如下实例在表 testalter_tbl 中添加 i 字段,并定义数据类型: 4.执行以上命令后,i 字段会自动添加到数据表字段的末尾

  • win10 mysql导出csv的两种方式

    win10 mysql导出csv的两种方式

    win10导出csv有两种方式,第一种是借助工具,Navicat for Mysql是一个非常好用的mysql可视化工具,可以导出多种格式的数据,但是也有其局限.第二种是原始SQL语句导出. Navicat For Mysql 安装软件,这里有绿色免费版,非常好用,链接:https://pan.baidu.com/s/1HZNzq_16M5yrq9hAMfNOmA 提取码:848h .打开软件,建立数据库连接,然后选择对应的表,右键->导出向导,选择格式即可. SQL导出 这里面有几个地方会出错

  • MySQL 隔离数据列和前缀索引的使用总结

    MySQL 隔离数据列和前缀索引的使用总结

    隔离数据列 通常,我们会发现查询语句会妨碍MySQL使用索引.除非在查询语句中列是独立的,否则MySQL不会使用这些列的索引."隔离"的意思是索引列不应该成为表达式的一部分或者在一个查询函数体中.例如下面的例子就不会命中actor_id这个索引. SELECT `actor_id` FROM `actor` WHERE `actor_id` + 1 = 2; 对于人来说,很容易知道查询条件实际是actor_id = 4,但是MySQL不会这么处理,因此养成简化WHERE判决条件的习惯,

  • MySQL 中行转列的方法

    MySQL行转列操作 所谓的行转列操作,就是将一个表的行信息转化为列信息,说着可能比较笼统,这里先举个例子,如下: +----+-----------+--------+-------+ | ID | USER_NAME | COURSE | SCORE | +----+-----------+--------+-------+ | | 张三 | 数学 | | | | 张三 | 语文 | | | | 张三 | 英语 | | | | 李四 | 数学 | | | | 李四 | 语文 | | | |

  • mysql alter语句用法实例

    //主键 ALTER TABLE tablename add new_field_id int(5) UNSIGNED DEFAULT 0 NOT NULL anto_increment,ADD PRIMARY KEY (new_field_id); //增加一个新列 mysql>ALTER TABLE tablename ADD fieldname fieldtype 如: ALTER TABLE t2 ADD d TIMESTAMP; ALTER TABLE infos ADD ex TIN

  • mysql alter table修改表命令整理

    MYSQL ALTER TABLE命令用于修改表结构,例如添加/修改/删除字段.索引.主键等等,本文章通过实例向大家介绍MYSQL ALTER TABLE语句的使用方法, MySQL ALTER语法如下: ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...] alter_specification: ADD [COLUMN] create_definition [FIRST | AFTER column_name ] or ADD

随机推荐