如何修改mysql数据库表结构

Online DDL 工具：pt-osc

对于 MySQL Online DDL 目前主流的有三种工具：

原生 Online DDL；

pt-osc(online-schema-change)，

gh-ost

本文主要讲解 pt-online-schema-change 的使用以及三种工具的简单对比。

一、原理及限制

1.1 原理

1. 创建一个与原表结构相同的空表，表名是 _new 后缀；

2. 修改步骤 1 创建的空表的表结构；

3. 在原表上加三个触发器：delete/update/insert，用于 copy 数据过程中，将原表中要执行的语句在新表中执行；

4. 将原表数据以数据块（chunk）的形式 copy 到新表；

5. rename 原表为 old 表，并把新表 rename 为原表名，然后删除旧表；

6. 删除触发器。

特别强调：每一次修改表结构都要从ALTER TABLE开始。

为Student表增加“入学时间”(S_entrance)列，其数据类型为日期型。

ALTER TABLE Student ADD S_entrance DATE

为KCXX表增加字段：“授课教师” ，数据类型为char(10)。

alter table KCXX add 授课教师 char(10)

为KCXX表增加identity列：ID(int类型、初始值1、增量1)。

alter table KCXX add ID int identity(1,1)

为Student表增加列出生年份：Sbirth，并设置为自动计算：2021-Sage。

alter table Student add Sbirth as 2021-Sage

将Student表的年龄(Sage)列的数据类型由改为整数。

ALTER TABLE Student ALTER COLUMN Sage INT

修改KCXX表的“授课教师” 字段名为teacher，数据类型为char(20)。 两步

sp_rename 'KCXX.授课教师' , 'teacher' ,'column'

alter table KCXX alter column teacher char(20)

3.增加表约束

为Course表的Ccredit列增加值为(1~4)的约束。

ALTER TABLE Course ADD CHECK (Ccredit>=1 AND Ccredit<=4) 或

ALTER TABLE Course ADD CONSTRAINT chk_credit CHECK (Ccredit>=1 AND Ccredit<=4)

为“价格”列创建check约束chk_JG，限制其值不能为空。

alter table 教材 add constraint chk_JG check(价格 is not null)

为课程表Course的课程名称(Cname)列增加必须取唯一值的约束条件。

ALTER TABLE Course ADD UNIQUE(Cname) 或

ALTER TABLE Course ADD CONSTRAINT U_name UNIQUE(Cname)

为Student表的列Ssex列增加默认值’男’。

alter table Student add default '男' for Ssex 或

alter table Student add constraint MoRen default '男' for Ssex

为教材表增加“书名” 为主键。

alter table 教材 add primary key(书名) 或

alter table 教材 add constraint PK_教材_书名 primary key(书名)

为表教材的所属学科列增加外键：参照表学科的学科编号。 一次只能增加一个外键约束！

alter table 教材 add foreign key(所属学科) references 学科(学科编号) 或

alter table 教材 add constraint FK_教材_学科 foreign key(所属学科) references 学科(学科编号)

为“类型”列创建名为chk_LX的check约束，限制其值只能为“新书”、或“二手书”。

alter table 教材 add constraint chk_LX check(类型 in ('新书','二手书'))

为教材表的“类型”列增加默认值“新书”。

alter table 教材 add default '新书' for 类型

删除XSDA1表里的Sbirth列

alter table XSDA1 drop column Sbirth

主键、外键、唯一值、默认值、check约束都是按约束constraint删除

删除教材表的外键。

alter table 教材 drop FK__教材__所属学科__66603565

删除教材表的约束。

alter table 教材 drop constraint chk_date

可以用一个alter table xscj drop constraint... 语句删除多个外键：

alter table xscj drop [constraint] fk1, fk2

（1）一个数据表只能定义一个PRIMARY KEY约束，但一个表中可根据需要对不同的列定义若干个UNIQUE约束。

（2）PRIMARY KEY字段的值不允许为NULL，而UNIQUE字段的值可取NULL。

（3）一般创建PRIMARY KEY约束时，系统会自动产生索引，索引的默认类型为簇索引。创建UNIQUE约束时，系统会自动产生一个UNIQUE索引，索引的默认类型为非簇索引。

先删除原来的外键

alter table xscj drop [constraint] FK_kcxx_课程编号

再增加新的带CASCADE的外键

alter table xscj add constraint FK_kcxx_课程编号 foreign key(课程编号) references kcxx(课程编号) on update cascade

先删除原来的外键

alter table xscj drop [constraint] FK_kcxx_课程编号

再增加新的带CASCADE的外键

alter table xscj add constraint FK_kcxx_课程编号 foreign key(课程编号) references kcxx(课程编号) on delete cascade

SQL语句中修改表结构的命令是ALTER TABLE。ALTER TABLE 语句用于在已有的表中添加、修改或删除列。

案例：表结构CREATE TABLE `login_user` ( `id` int(32) NOT NULL AUTO_INCREMENT, `name` varchar(225) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '名字'。

sql命令：

1、update ：用于更新表中的数据

update table_name

set goods_name=a,goods_id=b,...

WHERE some_column=some_value；

2、delete：用于删除表中的行

delete from table_name where some_column=some_value

注：where语句是指定哪条记录被更新，如果省略了则所有记录都被更新。

---