mysql中的ALTER TABLE功能

最新推荐文章于 2024-05-27 11:55:38 发布
最新推荐文章于 2024-05-27 11:55:38 发布
阅读量935 收藏 27
点赞数 12
文章标签： mysql 数据库 mysql中的ALTER
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zgscwxd/article/details/139233268
版权 

LTER TABLE `jshop_bill_delivery`
  ADD PRIMARY KEY (`delivery_id`) USING BTREE;

--
-- Indexes for table `jshop_bill_delivery_items`
--
ALTER TABLE `jshop_bill_delivery_items`
  ADD PRIMARY KEY (`id`) USING BTREE,
  ADD KEY `id` (`id`) USING BTREE;

--
-- Indexes for table `jshop_bill_delivery_order_rel`
--
ALTER TABLE `jshop_bill_delivery_order_rel`
  ADD PRIMARY KEY (`id`),
  ADD KEY `order_id` (`order_id`),
  ADD KEY `delivery_id` (`delivery_id`);


ALTER TABLE `jshop_bill_payments`
  ADD PRIMARY KEY (`payment_id`) USING BTREE,
  ADD KEY `payment_id` (`payment_id`) USING BTREE,
  ADD KEY `status` (`status`) USING BTREE,
  ADD KEY `type` (`type`) USING BTREE;

ALTER TABLE `jshop_bargain_log`
  MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `jshop_bargain_record`
--
ALTER TABLE `jshop_bargain_record`
  MODIFY `id` int(10) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `jshop_bill_aftersales_items`
--
ALTER TABLE `jshop_bill_aftersales_items`
  MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT;


ALTER TABLE `jshop_login_log`
  MODIFY `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id';
--
-- AUTO_INCREMENT for table `jshop_logistics`
--
ALTER TABLE `jshop_logistics`
  MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1100;

MYSQL 8.3 alter table 小记

mysql的alter table 本质上是通过创建临时表，然后将数据copy到临时表里，之后删除原来的表，重命名临时表 去做的alter操作

alter table 这个操作能干啥

  • 创建删除索引
  • 更改列类型
  • 重命名列或者表本身

重置自增列的起始值

不能设置值为当前DB中已经用到过的最大值。

alter table t21 auto_increment=13;

alter table 的算法

  • COPY
  • INPLACE

算法区别

  • copy 这个操作一行一行的copy数据从原始表到新表， 而且不支持并发DML，但是并发查询仍然是支持的，默认使用共享锁（可以并发查询），也可指定拍他锁（并发查询和DML都不支持）
  • inplace 这个操作会避免copy表数据，但是可能会重建表。在数据准备和执行阶段会进行行锁定，通常支持并发DML

mysql默认选择inplace，如果存储引擎不支持，就会选中copy。

InnoDB 对于在共享表空间中的表，使用的是COPY进行的alter table。此类会增加表的空间使用量，因为alter完成之后使用的额外空间并不会释放会操作系统。

使用INPLACE的算法场景包括：

  • alter table的操作被InnoDB online DDL的特性所支持。
  • 重命名表。 mysql会重命名table对应的文件，并不会做copy数据的操作。（per_file_on_table 需要打开）
  • 仅修改表的元数据的操作，包括： rename column，变更列的默认值，不改变数据类型存储空间的操作
  • 重命名索引
  • 添加或者删除一个二级索引

并发控制

LOCK= DEFAULT ｜ NONE ｜ SHARED ｜ EXCLUSIVE

  • DEFAULT 最大级别的并发。并发读和写 -> 并发读 -> 排它锁（依次降级）
  • NONE 并发读和写，不支持就报错
  • SHARED 并发读，不允许写。
  • EXCLUSIVE 不允许并发读和写。

修改列的三种方式

  • change
    • 能够重命名列和更改列的定义，或者同时进行
    • 有更多的能力比modify，但是在某些方面会比较慢。 比如： 如果不是重命名的话， change会对列命名两次
    • 可以通过first/ after 更改列顺序
  • modify
    • 能够改变列定义，但是不能改变列的名称。
    • 不是重命名的时候比change更加方便
    • 可以通过first/ after 更改列顺序
  • alter
    • 用来更改列的默认值

alter table temp_table change name name_new varchar(100);
alter table temp_table change name name varchar(100);
alter table temp_table modify name varchar(200) after id ;
alter table temp_table alter name set default '';

注：

change是mysql对标准sql的拓展， modify是mysql对oracel的兼容。
modifyor change 去更改数据类型的时候，mysql会尝试转化存在的列的值到新的类型中。

注意： 通过change重命名列

  • 自动变更
    • 指向旧列的索引
    • 外键
  • 不会自定变更
    • 生成的列和分块的表达式
    • 视图和存储过程

MySQL 使用 ALTER TABLE 语句修改表。修改表指的是修改数据库中已经存在的数据表的结构。常用的修改表的操作有修改表名、修改字段数据类型或字段名、增加和删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等。

常用的语法格式如下：
ALTER TABLE <表名> [修改选项]

修改选项的语法格式如下：

1

2

3

4

5

6

{ ADD COLUMN <列名> <类型>

| CHANGE COLUMN <旧列名> <新列名> <新列类型>

| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }

| MODIFY COLUMN <列名> <类型>

| DROP COLUMN <列名>

| RENAME TO <新表名> }

添加字段

添加字段的语法格式如下：
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST|AFTER 已存在的字段名]；

【例 1】使用 ALTER TABLE 修改表 temp_table 的结构，在表的第一列添加一个 int 类型的字段 newcol，输入的 SQL 语句和运行结果如下所示。

mysql> ALTER TABLE temp_table
-> ADD COLUMN newcol INT FIRST;

显示结果：

1

2

3

4

5

6

7

8

9

10

11

+--------+-------+------+-----+---------+----------------+

| Field  | Type  | Null | Key | Default | Extra          |

+--------+-------+------+-----+---------+----------------+

| newcol | int   | YES  |     | NULL    |                |

| id     | int   | NO   | PRI | NULL    | auto_increment |

| x      | float | YES  |     | NULL    |                |

| y      | float | YES  |     | NULL    |                |

| z      | float | YES  |     | NULL    |                |

| T      | float | YES  |     | NULL    |                |

| V      | float | YES  |     | NULL    |                |

+--------+-------+------+-----+---------+----------------+

提示1：ADD COLUMN newcol INT FIRST;  这里的COLUMN 可以省略。可写成：ADD newcol INT FIRST;

提示2：“FIRST 或 AFTER 已存在的字段名”用于指定新增字段在表中的位置，如果 SQL 语句中没有这两个参数，则默认将新添加的字段设置为数据表的最后列。

【例 2】使用 ALTER TABLE 修改表 temp_table 的结构，在一列 newcol后添加一个 float 类型的字段 newcol2，输入的 SQL 语句和运行结果如下所示。

mysql> ALTER TABLE temp_tabel
-> ADD newcol2 float AFTER newcol;

显示结果：

+---------+-------+------+-----+---------+----------------+
| Field   | Type  | Null | Key | Default | Extra          |
+---------+-------+------+-----+---------+----------------+
| newcol  | int   | YES  |     | NULL    |                |
| newcol2 | float | YES  |     | NULL    |                |
| id      | int   | NO   | PRI | NULL    | auto_increment |
| x       | float | YES  |     | NULL    |                |
| y       | float | YES  |     | NULL    |                |
| z       | float | YES  |     | NULL    |                |
| T       | float | YES  |     | NULL    |                |
| V       | float | YES  |     | NULL    |                |
+---------+-------+------+-----+---------+----------------+

修改字段数据类型

修改字段的数据类型就是把字段的数据类型转换成另一种数据类型。在 MySQL 中修改字段数据类型的语法规则如下：
ALTER TABLE <表名> MODIFY <字段名> <数据类型>
其中，表名指要修改数据类型的字段所在表的名称，字段名指需要修改的字段，数据类型指修改后字段的新数据类型。

【例 3】使用 ALTER TABLE 修改表 temp_table 的结构，将 newcol2 字段的数据类型由 float 修改成 int，输入的 SQL 语句和运行结果如下所示。

mysql> ALTER TABLE temp_table
-> MODIFY newcol2 int;

显示结果：

+---------+-------+------+-----+---------+----------------+
| Field   | Type  | Null | Key | Default | Extra          |
+---------+-------+------+-----+---------+----------------+
| newcol  | int   | YES  |     | NULL    |                |
| newcol2 | int   | YES  |     | NULL    |                |
| id      | int   | NO   | PRI | NULL    | auto_increment |
| x       | float | YES  |     | NULL    |                |
| y       | float | YES  |     | NULL    |                |
| z       | float | YES  |     | NULL    |                |
| T       | float | YES  |     | NULL    |                |
| V       | float | YES  |     | NULL    |                |
+---------+-------+------+-----+---------+----------------+

删除字段

ALTER TABLE <表名> DROP <字段名>；

【例 4】使用 ALTER TABLE 修改表 temp_table 的结构，删除 newcol2 字段，输入的 SQL 语句和运行结果如下所示。

mysql> ALTER TABLE temp_table
-> DROP newcol2;

显示结果：

+--------+-------+------+-----+---------+----------------+
| Field  | Type  | Null | Key | Default | Extra          |
+--------+-------+------+-----+---------+----------------+
| newcol | int   | YES  |     | NULL    |                |
| id     | int   | NO   | PRI | NULL    | auto_increment |
| x      | float | YES  |     | NULL    |                |
| y      | float | YES  |     | NULL    |                |
| z      | float | YES  |     | NULL    |                |
| T      | float | YES  |     | NULL    |                |
| V      | float | YES  |     | NULL    |                |
+--------+-------+------+-----+---------+----------------+

修改字段名称

MySQL 中修改表字段名的语法规则如下：
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>；

其中，旧字段名指修改前的字段名；新字段名指修改后的字段名；新数据类型指修改后的数据类型，如果不需要修改字段的数据类型，可以将新数据类型设置成与原来一样，但数据类型不能为空。

【例 5】使用 ALTER TABLE 修改表 temp_table 的结构，将 newcol2 字段名称改为 col2，同时将数据类型变为 float，输入的 SQL 语句和运行结果如下所示。

mysql> ALTER TABLE temp_table
-> CHANGE newcol2 col2 float;

显示结果：

+--------+-------+------+-----+---------+----------------+
| Field  | Type  | Null | Key | Default | Extra          |
+--------+-------+------+-----+---------+----------------+
| newcol | int   | YES  |     | NULL    |                |
| col2   | float | YES  |     | NULL    |                |
| id     | int   | NO   | PRI | NULL    | auto_increment |
| x      | float | YES  |     | NULL    |                |
| y      | float | YES  |     | NULL    |                |
| z      | float | YES  |     | NULL    |                |
| T      | float | YES  |     | NULL    |                |
| V      | float | YES  |     | NULL    |                |
+--------+-------+------+-----+---------+----------------+

CHANGE 也可以只修改数据类型，实现和 MODIFY 同样的效果，方法是将 SQL 语句中的“新字段名”和“旧字段名”设置为相同的名称，只改变“数据类型”。

提示：由于不同类型的数据在机器中的存储方式及长度并不相同，修改数据类型可能会影响数据表中已有的数据记录，因此，当数据表中已经有数据时，不要轻易修改数据类型。

修改表名

ALTER TABLE <旧表名> RENAME [TO] <新表名>；

其中，TO 为可选参数，使用与否均不影响结果。

zgscwxd
关注
mysql alter table add column的几个性能知识点
Yu's Notes
07-01 7万+
今天调查了一个问题，背景是在aws的Aurora MySQL上，三百万数据的表里追加列，测试了中间指定位置追加，和默认的末尾追加，发现速度都挺快的。 一般印象里在中间追加应该比默认的情况要慢一些，结果是反而稍微快一点。就想要深入得了解一下。以下为内容。 问题：在追加列时，①default追加到末尾与②指定位置追加两种 性能有什么区别。 语法： ①default默认无需指定位置，会追加到末尾 ...
MySQL如何创建主键？Alter table add PRIMARY KEY的用法和例子
JackLiu16的博客
06-18 1万+
-- 创建一个无主键的表格  create table testNoPK (    id int not null,    name varchar(10)   );     -- 创建索引   alter table testNoPk      add primary key PK_testNoPK (id);  alter table tb_name change add add int(10...
Mysql命令alter add：增加表的字段
dxyzhbb的博客
12-04 6076
在增加字段前，先使用选择数据库命令 选择数据库，命令为"use 库名 ；" alter add命令用来增加表的字段。 alter add命令格式：alter table 表名 add字段 类型 其他; 例如，在表MyClass中添加了一个字段passtest，类型为int(4)，默认值为0： mysql> alter table MyClass add passtest int(4) default '0'; mysql查询表结构命令 1.-- 查看表结构　　DESC 表名;1) 加..
mysql增加primary key_oracel Alter table 给表增加主键 primary key
weixin_33406123的博客
01-27 498
sql>sql> create table emp (empno number(4) not null,2 ename varchar2(10),3 job varchar2(9),4 mgr number(4),5 hiredate ...
mysql alter table命令修改表结构实例详解
12-18
 实例一：使用ALTER TABLE命令向表中添加字段、修改字段类型以及设置主键。 首先创建一个表，SQL语句如下： mysql> CREATE TABLE myTable( -> ID SMALLINT -> ); 使用desc命令查看表结构： mysql> desc myTable...
MySQL中的alter table命令的基本使用方法及提速优化
01-21
mysql>alter table pet add weight int; 2. 删除列 alter table tbl_name drop col_name 例如, 删除pet表中的weight这一列 mysql>alter table pet drop weight; 3. 改变列 分为改变列的属性和改变列的名字 改变...
mysql alter table命令修改表结构实例
12-15
 实例一：使用ALTER TABLE命令向表中添加字段、修改字段类型以及设置主键。 首先创建一个表，SQL语句如下： mysql> CREATE TABLE myTable( -> ID SMALLINT -> ); 使用desc命令查看表结构： mysql> desc ...
mysql alter table 修改表命令详细介绍
09-09
MySQL的`ALTER TABLE`命令是数据库管理中不可或缺的一部分，它允许用户在不丢失已有数据的情况下，对表结构进行各种修改。本文将深入探讨`ALTER TABLE`的使用，包括添加、修改和删除字段、索引、主键等操作，并通过...
mysql alter 最佳实践总结_alter table add key
最新发布
zgscwxd的博客
05-27 369
mysql alter 最佳实践总结_alter table add key
MySQL中的alter table操作之add/modify/drop列
一缕阳光的博客
07-19 1万+
alter table的操作有增加列、删除列和修改列的长度等 create table t1 (c1 int primary key) engine = innodb; // 增加一个列c2 alter table t1 add c2 varchar(16379); drop table t1; create table t1 (c1 int primary key, c2 varchar(50)) engine = innodb; // 将列c2长度更改 alter table t1 modif
mysql alter table add constraint_add constraint的用法
weixin_33091939的博客
02-05 3921
1.主键约束：要对一个列加主键约束的话，这列就必须要满足的条件就是分空因为主键约束：就是对一个列进行了约束，约束为(非空、不重复)以下是代码要对一个列加主键，列名为id,表名为emp格式为：alter table 表格名称 add constraint 约束名称 增加的约束类型 (列名)例子：alter table emp add constraint ppp primary key (id)—...
MySQL 新增字段（alter table add column)，报错：Duplicate entry
学无止境
06-29 6644
执行一个增加字段的DDL语句脚本时，报错，Error 1062: Duplicate entry
MySQL数据库——MySQL主键（PRIMARY KEY）
热门推荐
Itmastergo的博客
04-19 1万+
主键（PRIMARY KEY）的完整称呼是“主键约束”，是MySQL 中使用最为频繁的约束。一般情况下，为了便于 DBMS 更快的查找到表中的记录，都会在表中设置一个主键。
Mysql命令增加、修改、删除表字段
bhegi_seg的博客
08-23 4448
/增加一个字段，默认为空。//增加一个字段，默认不能为空。//修改一个字段的名称，此时一定要重新指定该字段的类型。
mysql中alter table的用法
09-28
mysql中alter table语句用于修改表的基本结构，包括添加字段、删除字段、添加主键、添加索引、修改字段数据类型、对表重命名等操作。以下是alter table语句的使用方法示例： 1. 添加字段： ALTER TABLE 表名 ADD 列名 数据类型; 2. 删除字段： ALTER TABLE 表名 DROP 列名; 3. 修改字段数据类型： ALTER TABLE 表名 MODIFY 列名 新数据类型; 4. 修改字段名称： ALTER TABLE 表名 CHANGE 原列名 新列名 数据类型; 5. 添加主键： ALTER TABLE 表名 ADD PRIMARY KEY (列名); 6. 添加索引： ALTER TABLE 表名 ADD INDEX 索引名 (列名); 7. 修改表名： ALTER TABLE 原表名 RENAME TO 新表名; 8. 初始化AUTO_INCREMENT的值： ALTER TABLE 表名 AUTO_INCREMENT = 1; 注意：以上只是一些常用的alter table语句示例，实际使用中还可以根据需要添加其他操作。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值