MySQL-SQL-DDL 数据定义语言

13.1 Data Definition Statements
https://dev.mysql.com/doc/refman/5.7/en/sql-data-definition-statements.html

---

字段名避免使用mysql关键字

创建一个字典表，字段名 使用了 key, 加反引号执行 DDL 的时候没问题

CREATE TABLE `dict`(
    `id`                 bigint(20)   NOT NULL AUTO_INCREMENT,
    `key`                varchar(255) NOT NULL,
    `value`              text         NOT NULL,
    `description`        text         DEFAULT NULL,
    `enabled`            BOOLEAN      DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

但 jpa auto ddl 时生成的 sql 字段名是不带反引号的，执行sql就会报错， key varchar(255) 处会报错，和 mysql 创建索引的关键字 KEY 冲突了。

create table dict
(
    id                 bigint not null auto_increment,
    description        varchar(255),
    key varchar(255),
    status             varchar(255) default 'enabled',
    value              varchar(255),
    primary key (id)
) engine = InnoDB

DDL与事务

MySQL不支持事务型DDL

MySQL DDL 不支持事务，DDL语句执行后会立即提交。
所以 drop table, create table, alter table 这些 DDL 是不支持事务的。

13.3.2 Statements That Cannot Be Rolled Back
https://dev.mysql.com/doc/refman/5.7/en/cannot-roll-back.html

常见数据库的事务DDL支持情况

PostgreSQL 的这篇调研说明了主要数据库是否支持 事务DDL 以及为什么。
Transactional DDL in PostgreSQL: A Competitive Analysis
https://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis

总结如下：
PostgreSQL - yes
MySQL - no; DDL causes an implicit commit
Oracle Database 11g Release 2 and above - by default, no, but an alternative called edition-based redefinition exists
Older versions of Oracle - no; DDL causes an implicit commit
SQL Server - yes
Sybase Adaptive Server - yes
DB2 - yes
Informix - yes
Firebird (Interbase) - yes

Is it possible to roll back CREATE TABLE and ALTER TABLE statements in major SQL databases?
https://stackoverflow.com/questions/4692690/is-it-possible-to-roll-back-create-table-and-alter-table-statements-in-major-sql

MySQL事务中混合DDL会怎样？

应当将 DDL 和 DML 语句以及 DCL 语句严格分开，避免事务被隐性“破坏”，导致误操作情况发生。

#禁用自动提交
set autocommit=off;
#创建tb1
create table tb1(id int auto_increment primary key,c1 int);
#开始事务
start transaction;
#插入数据
insert into tb1(c1) select 1;
insert into tb1(c1) select 2;
insert into tb1(c1) select 3;
#创建tb2
create table tb2(id int auto_increment primary key,c1 int);

执行完上述 sql 后，如果想回滚3条插入操作，会发现无法将这3条数据删除，因为 create table tb2 这条 DDL 执行完后会自动提交，顺带也会把之前的 DML 提交。

当执行到DDL语句时，会隐式的将当前回话的事务进行一次“COMMIT”操作，因此在MySQL中执行DDL语句时，应该严格地将DDL和DML完全分开，不能混合在一起执行。

---

MySQL的三种DDL处理方式

MySQL 各版本，对于 DDL 的处理方式是不同的，主要有三种：

Copy Table(可读不可写)

Copy Table 方式， 这是 InnoDB 最早支持的方式。顾名思义，通过临时表拷贝的方式实现的。新建一个带有新结构的临时表，将原表数据全部拷贝到临时表，然后 rename，完成创建操作。这个方式过程中，原表是可读的，不可写。但是会消耗一倍的存储空间。

处理过程：
1、首先新建 Temp table，表结构是 ALTAR TABLE 新定义的结构
2、然后把原表中数据导入到这个 Temp table
3、删除原表
4、最后把临时表 rename 为原来的表名
为了保持数据的一致性，中间复制数据（Copy Table）全程锁表只读，如果有写请求进来将无法提供服务，连接数爆张。

在 MySQL 5.1（带InnoDB Plugin）和 5.5 中，有个新特性叫 Fast Index Creation(FIC)，就是在添加或者删除二级索引的时候，可以不用复制原表。
引入 FIC 之后，创建二级索引时会对原表加上一个S锁，创建过程不需要重建表（no-rebuild）；删除 InnoDB 二级索引只需要更新内部视图，并标记这个索引的空间可用，去掉数据库元数据上该索引的定义即可。这个过程也只允许读操作，不能写入，但大大加快了修改索引的速度（不含主键索引，InnoDB 的按主键聚簇存储特性决定了修改主键依然需要 Copy Table ）。

FIC 只对索引的创建删除有效，MySQL 5.6 Online DDL 把这种特性扩展到了添加列、删除列、修改列类型、列重命名、设置默认值等等，实际效果要看所使用的选项和操作类别来定。

Inplace(可读不可写)

Inplace 方式，这是原生 MySQL 5.5，以及 innodb_plugin 中提供的方式。所谓 Inplace 也就是在原表上直接进行，不会拷贝临时表。相对于 Copy Table 方式，这比较高效率。原表同样可读的，但是不可写。

Online(可读可写,5.6及以上)

Online 这是 MySQL 5.6 以上版本中提供的方式。无论是 Copy Table 方式，还是 Inplace 方式，原表只能允许读取，不可写。对应用有较大的限制，因此 MySQL 最新版本中，InnoDB 支持了所谓的 Online 方式 DDL. 与以上两种方式相比，online 方式支持 DDL 时不仅可以读，还可以写，对于 dba 来说，这是一个非常棒的改进。

---

MySQL5.6 中的 InnoDB Online DDL

14.13 InnoDB and Online DDL
https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html

14.13 InnoDB 在线 DDL - 官方文档的中文翻译
https://zhuanlan.zhihu.com/p/40443907

从 MySQL 5.6 开始，InnoDB 存储引擎提供一种叫 在线 DDL(Online DDL) 的 DDL 执行方式，允许 Inplace 更改表 和 并发 DML 操作。
此功能的好处包括：

• 在繁忙的生产环境中提高响应能力和可用性，使表不可用几分钟或几小时是不切实际的。
• 使用 LOCK 子句在 DDL 操作期间调整性能和并发性之间平衡的能力。请参阅 LOCK 子句。
• 与 table-copy 方法相比，使用的磁盘空间和 I/O 开销更少。

Online DDL 基于 MySQL 5.5 开始提供的 **快速索引创建特性(fast index creation)**，快速索引创建特性可以在不拷贝表的情况下进行索引创建和删除。

Online DDL 是默认开启的，无需执行任何特殊操作即可启用在线 DDL. 默认情况下，MySQL 在允许的情况下执行操作，并尽可能少地锁定。

可以使用 ALTER TABLE 语句的 LOCK 和 ALGORITHM 子句控制 DDL 操作的各个方面。
这些子句放在语句的末尾，用逗号分隔表和列。 例如：

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

LOCK 子句可用于微调对表的并发访问程度。
ALGORITHM 子句主要用于性能比较，并且在遇到任何问题时作为旧表复制行为的后备。
例如：

• 为避免意外地使表不可用于读取，写入或两者，请在 ALTER TABLE 语句中指定一个子句，例如 LOCK = NONE（允许读取和写入）或 LOCK = SHARED（允许读取）。 如果请求的并发级别不可用，则操作立即停止。
• 要比较性能，请运行 ALGORITHM = INPLACE 和 ALGORITHM = COPY 语句。
• 为避免使用复制表的 ALTER TABLE 操作来绑定服务器，请包括 ALGORITHM = INPLACE。 如果语句不能使用 in-place 机制，则该语句立即停止。

---

Online DDL 选项

MySQL 在线 DDL 分为 INPLACE 和 COPY 两种方式，通过在 ALTER 语句的 ALGORITHM 参数指定。

ALGORITHM=INPLACE，原地操作，可以避免重建表带来的 IO 和 CPU 消耗，保证 DDL 期间依然有良好的性能和并发。
ALGORITHM=COPY，需要拷贝原始表，所以不允许并发 DML 写操作，可读。这种 copy 方式的效率不如 inplace, 因为前者需要记录 undo 和 redo log, 而且因为临时占用 buffer pool 引起短时间内性能受影响。

上面只是 Online DDL 内部的实现方式，此外还有 LOCK 选项控制是否锁表，根据不同的 DDL 操作类型有不同的表现：默认 mysql 尽可能不去锁表，但是像修改主键这样的昂贵操作不得不选择锁表。

LOCK=NONE，即 DDL 期间允许并发读写涉及的表，比如为了保证 ALTER TABLE 时不影响用户注册或支付，可以明确指定，好处是如果不幸该 alter 语句不支持对该表的继续写入，则会提示失败，而不会直接发到库上执行。ALGORITHM=COPY 默认 LOCK 级别
LOCK=SHARED，即 DDL 期间表上的写操作会被阻塞，但不影响读取。
LOCK=DEFAULT，让 mysql 自己去判断 lock 的模式，原则是 mysql 尽可能不去锁表
LOCK=EXCLUSIVE，即 DDL 期间该表不可用，堵塞任何读写请求。如果你想 alter 操作在最短的时间内完成，或者表短时间内不可用能接受，可以手动指定。

但是有一点需要说明，无论任何模式下，online ddl 开始之前都需要一个短时间排它锁(exclusive)来准备环境，所以 alter 命令发出后，会首先等待该表上的其它操作完成，在 alter 命令之后的请求会出现等待waiting meta data lock。同样在ddl结束之前，也要等待alter期间所有的事务完成，也会堵塞一小段时间。所以尽量在 ALTER TABLE 之前确保没有大事务在执行，否则一样出现连环锁表。

mysql 5.6 在线 DDL
https://www.cnblogs.com/wyy123/p/10272496.html

---

Online DDL 索引操作

创建或增加二级索引，删除索引，重命名索引都支持 in-place 的方式，均支持并发 DML，但是不能重建表。其中，删除索引和重命名索引只修改元数据。

创建和增加二级索引

CREATE INDEX name ON table (col_list);
ALTER TABLE tbl_name ADD INDEX name (col_list);

在创建索引时，该表仍可用于读写操作。 CREATE INDEX 语句仅在完成访问表的所有事务完成后才结束，因此索引的初始状态反映了表的最新内容。
在线DDL支持添加二级索引意味着您通常可以通过先创建没有二级索引的表，再加载数据，最后添加二级索引，来加速创建和加载表及相关索引的整个过程。
新创建的辅助索引仅包含CREATE INDEX或ALTER TABLE语句完成执行时表中的已提交数据。 它不包含任何未提交的值，旧版本的值或标记为删除但尚未从旧索引中删除的值。
如果服务器在创建二级索引时退出，则在恢复时，MySQL会删除任何部分创建的索引。 您必须重新运行ALTER TABLE或CREATE INDEX语句。
某些因素会影响此操作的性能，空间使用和语义。

删除索引

DROP INDEX name ON table;
ALTER TABLE tbl_name DROP INDEX name;

在删除索引时，该表仍可用于读写操作。 DROP INDEX语句仅在完成访问表的所有事务完成后才结束，因此索引的初始状态反映了表的最新内容。

14.13.1 Online DDL Operations
https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html

MySQL给已存在的表增加索引时会锁表吗？

看版本，MySQL 5.6 及以上的话，支持 Online DDL 操作，不会锁表。
MySQL 5.6 以下版本，不支持 Online DDL 操作，会锁表

---

Online DDL 主键操作

增加主键（原来无显式主键），删除后再增加主键，都支持in-place，重建表，可并发DML，并不仅仅只修改元数据。
删除主键 不支持并发DML和in-place，并不仅仅只修改元数据。

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

修改主键需要重建表，因为 innodb 是按主键聚簇存储的，需要大量数据重组操作，需要复制表，因此，最好在创建表时定义主键，而不是稍后发出ALTER TABLE … ADD PRIMARY KEY
如果你创建一个没有主键的表，InnoDB会为你选择一个，它可以是在NOT NULL列上定义的第一个UNIQUE键，或者是系统生成的键。 为避免不确定性以及额外隐藏列的潜在空间要求，请将PRIMARY KEY子句指定为CREATE TABLE语句的一部分。

主键修改过程：
MySQL通过将原始表中的现有数据复制到具有所需索引结构的临时表来创建新的聚簇索引。 将数据完全复制到临时表后，把原始表重命名为一个新的临时表，然后把刚才的临时表重命名为原始表名，然后删除原始表。

14.13.1 Online DDL Operations
https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html

---

Online DDL 字段操作

除了改变字段类型以外的字段操作，均支持并发 DML。所有的操作都支持 in-place 的方式。

增加字段

ALTER TABLE tbl_name ADD COLUMN column_name column_definition, ALGORITHM=INPLACE, LOCK=NONE;

注意：添加自增列时不允许并发DML。
尽管允许 ALGORITHM=INPLACE ，但数据大幅重组，所以它仍然是一项昂贵的操作。当添加列是auto-increment，不允许DML并发

删除字段

ALTER TABLE tbl_name DROP COLUMN column_name, ALGORITHM=INPLACE, LOCK=NONE;

删除列需要进行大量数据重组，是一项开销很大的操作。

14.13.1 Online DDL Operations
https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html

14.13 InnoDB 在线 DDL - 官方文档的中文翻译
https://zhuanlan.zhihu.com/p/40443907

---

第三方Schema迁移工具

pt-online-schema-change

pt-online-schema-change
https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html

gh-ost

github / gh-ost
https://github.com/github/gh-ost

不需要触发器支持的 MySQL 在线更改表结构的工具

所有在线表结构修改工具的操作方式都类似：
创建与原表结构一致的临时表，该临时表已经是按要求修改后的表结构了，缓慢增量的从原表中复制数据，同时记录原表的更改(所有的 INSERT, DELETE, UPDATE 操作) 并应用到临时表。当工具确认表数据已经同步完成，它会进行替换工作，将临时表更名为原表。

---

索引

show index from table查看索引

show index from table_name;
或
show keys from table_name;

Non_unique 是否非唯一，0不是，1是
Key_name 索引的名称。
Seq_in_index 索引中的列序列号，从1开始。
Column_name 列名称。
Collation 列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。
Cardinality 索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
Sub_part 如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
Packed 指示关键字如何被压缩。如果没有被压缩，则为NULL。
Null 如果列含有NULL，则含有YES。如果没有，则该列含有NO。
Index_type 用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。

MariaDB [uds]> show index from user_address;
+--------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user_address |          0 | PRIMARY     |            1 | id          | A         |        2807 |     NULL | NULL   |      | BTREE      |         |               |
| user_address |          1 | user_id     |            1 | user_id     | A         |        2807 |     NULL | NULL   |      | BTREE      |         |               |
| user_address |          1 | province_id |            1 | province_id | A         |          56 |     NULL | NULL   | YES  | BTREE      |         |               |
| user_address |          1 | city_id     |            1 | city_id     | A         |         140 |     NULL | NULL   | YES  | BTREE      |         |               |
| user_address |          1 | region_id   |            1 | region_id   | A         |         255 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.012 sec)

创建表时添加索引

-- 车辆信息表
DROP TABLE IF EXISTS `vehicle_info`;
CREATE TABLE `vehicle_info` (
  `id`                    BIGINT       NOT NULL AUTO_INCREMENT,
  `car_order_id`          BIGINT       NOT NULL COMMENT '整车订单id',
  `car_order_no`          VARCHAR(32)  NOT NULL COMMENT '整车订单订单号',
  `vehicle_id`            VARCHAR(32)  COMMENT '车辆id', 
  `vin_code`              VARCHAR(40)  COMMENT '车辆vin码',
  `create_time`           DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time`           TIMESTAMP    NOT NULL COMMENT '修改时间',
  PRIMARY KEY (`id`),
  FOREIGN KEY (`car_order_id`) REFERENCES `car_order` (`id`),
  UNIQUE KEY (`car_order_id`),
  KEY `idx_car_order_no`(`car_order_no`),
  KEY `idx_vehicle_id` (`vehicle_id`),
  KEY `idx_vin_code` (`vin_code`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

create index在现有表上添加索引

CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

前缀索引(最大3072字节)

InnoDB 索引长度上限是 767 字节，当启用 innodb_large_prefix 时，上限可以达到 3072 字节。
如果字段过长，可以只使用前缀创建索引，即前缀索引。
如果是 utf8 编码，占 3 个字节，可以索引最大 3072/3=1024 个字符。
如果是 utf8mb4 编码，占 4个字节，最大可索引 3072/4=768 个字符。

ALTER TABLE table_name ADD index index_name (column_name(prefix_length));
alter table page_view add index pathname (`pathname`(255));

MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)

[42000][1071] Specified key was too long; max key length is 3072 bytes

key和index区别

KEY
key 是数据库的物理结构，它包含两层意义，一是约束（偏重于约束和规范数据库的结构完整性），二是索引（辅助查询用的）。包括primary key, unique key, foreign key 等。

• primary key 有两个作用，一是约束作用（constraint），用来规范一个存储主键和唯一性，但同时也在此key上建立了一个index；
• unique key 也有两个作用，一是约束作用（constraint），规范数据的唯一性，但同时也在这个key上建立了一个index；
• foreign key也有两个作用，一是约束作用（constraint），规范数据的引用完整性，但同时也在这个key上建立了一个index；
  MySQL中的key是同时具有constraint和index的意义。

另外，在MySQL中，对于一个Primary Key的列，MySQL已经自动对其建立了Unique Index，无需重复再在上面建立索引了。

INDEX
index是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储。索引要分类的话，分为前缀索引、全文本索引等；因此，索引只是索引，它不会去约束索引的字段的行为。

---

create table

查看建表语句

show create table 查看建表语句

show create table table_name;

show create database 查看建库语句

show create database database_name;

desc 查看列

desc table_name;

---

AUTO_INCREMENT 自增值设置与修改

创建表时设置自增主键：，默认起始值是 1

CREATE TABLE `orders` (
  `order_num` int(11) NOT NULL auto_increment,
  `order_date` datetime NOT NULL,
  `cust_id` int(11) NOT NULL,
  PRIMARY KEY  (`order_num`),
  KEY `fk_orders_customers` (`cust_id`),
  CONSTRAINT `fk_orders_customers` FOREIGN KEY (`cust_id`) REFERENCES `customers` (`cust_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8;

创建表格后添加：

alter table users AUTO_INCREMENT=10000;

而且该语句也适用于修改现有表的id上， 比如大批量删除数据后，想id从654321退回123456开始

alter table users AUTO_INCREMENT=123456;

13.1.17 CREATE TABLE Statement - AUTO_INCREMENT
https://dev.mysql.com/doc/refman/5.6/en/create-table.html

---

拷贝创建新表

创建新表同时拷贝数据(丢失约束等)

复制表结构及数据到新表
CREATE TABLE 新表 SELECT * FROM 旧表
这种方法会将oldtable中所有的内容都拷贝过来，当然我们可以用delete from newtable;来删除。
不过这种方法的一个最不好的地方就是新表中没有了旧表的primary key、Extra（auto_increment）等属性。需要自己用alter table语句添加，而且容易搞错。

只拷贝表结构(丢失约束等)

只复制表结构到新表
CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2
其中的where只要是个恒为false的表达式即可，此方法同样会丢失约束等额外信息

只拷贝表结构(保留约束等)

CREATE TABLE 新表 LIKE 旧表
此方法会保留表的约束等所有额外信息，推荐使用

创建新表并复制数据(保留约束等)

完整复制表的方法:
先创建新表，保留约束等，在插入旧表的所有数据
CREATE TABLE targetTable LIKE sourceTable;
INSERT INTO targetTable SELECT * FROM sourceTable;

例如

create table user_bk like user;
insert into user_bk select * from user;

MySQL复制表结构表数据
https://www.cnblogs.com/emanlee/p/5140670.html

---

alter table

add column 添加列

alter table table_name
add column column_name varchar(30);

alter table table_name
add column `data_type` TINYINT NOT NULL DEFAULT 1 COMMENT '数据类型 0:unknown 1:male 2:owner';

drop column 删除列

ALTER TABLE table_name
DROP COLUMN field_name;

或者不加column关键字也行

ALTER TABLE table_name
DROP field_name;

删除的列不能是约束的一部分

注意：要删除的列不能是约束的一部分，比如 group_type 是多列唯一约束的其中一列，则删除时提示找不到列：
ERROR 1072 (42000): Key column ‘group_type’ doesn’t exist in table
需要先将唯一约束删除才能继续。

add index/key 添加索引

使用Alter语法:

ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE [KEY|INDEX] [index_name](column_list)
ALTER TABLE table_name ADD PRIMARY KEY [index_name](column_list)

例如:

alter table user add unique (mobile, country_code);
alter table user add unique key (mobile, country_code);
alter table user add unique index (mobile, country_code);
alter table user add unique index mobile(mobile, country_code);
alter table page_view_transaction add index `pathname` (`pathname`(255));
alter table page_view_transaction add index host (host);

drop index/key 删除索引

DROP INDEX index_name ON talbe_name;
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

同一条alter语句中删除索引，同时添加索引

ALTER TABLE `dbname`.`table_name`
  DROP INDEX `idx_tppa_userid`,
  ADD UNIQUE `idx_tppa_userid` USING BTREE (`user_id`) comment '';

MySQL 中没有 drop constraint

注意：mysql 没有 DROP CONSTRAINT 语法，必须使用 drop index/key

>alter table user drop constraint UK_name;
[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'constraint UK_name' at line 1

first/after 添加列时指定字段顺序

FIRST，添加为第一列
AFTER col_name，添加到指定列名后
默认是添加为最后一列

ALTER TABLE user
ADD column_name varchar(128) null COMMENT '姓名' FIRST;

ALTER TABLE user
ADD column_name varchar(128) null COMMENT '姓名' AFTER id;

https://dev.mysql.com/doc/refman/5.6/en/alter-table.html#alter-table-add-drop-column

同时添加/删除/修改列和索引

可在同一个 ALTER TABLE 语句中同时指定多个修改子句，例如添加多列，或者添加多列、删除多列、添加索引等组合在一起，都可以：

1、同时删除多列

alter table user
drop column email,
drop column education,
drop column job,
drop column marriage,
drop column phone;

2、列和索引一起删除：

alter table user
  drop COLUMN user_uuid,
  drop COLUMN code,
  drop COLUMN name,
  drop COLUMN age,
  drop KEY user_uuid;

3、同时添加多列和索引

ALTER TABLE table_name
  ADD COLUMN `uuid` BIGINT NOT NULL AFTER `id`,
  ADD COLUMN `code` VARCHAR(10) COMMENT '编码' AFTER uuid,
  ADD COLUMN `name` VARCHAR(20) COMMENT '名字' AFTER code,
  ADD UNIQUE KEY (uuid);

4、同时添加多列、修改列、添加索引

ALTER TABLE table_name
  ADD COLUMN `uuid` BIGINT NOT NULL AFTER `id`,
  ADD COLUMN `code` VARCHAR(10) COMMENT '编码' AFTER uuid,
  ADD COLUMN `name` VARCHAR(20) COMMENT '名字' AFTER code,
  MODIFY COLUMN `enabled` BOOLEAN NOT NULL DEFAULT TRUE,
  ADD UNIQUE KEY (uuid);

change column 修改列名

change column 可以修改列名、列数据类型，或者调整顺序

alter table table_name
CHANGE COLUMN old_name new_name BIGINT NOT NULL COMMENT '用户id'

modify column 调整列顺序

modify column 可以列数据类型，或者调整顺序，但不能修改列名。
column_name 放到 column_name2 后面

alter table table_name
modify column column_name tinyint(4) NOT NULL DEFAULT '0' after column_name2;

modify column 修改列类型

无论 column_name 原来是什么类型，直接改为想要的类型

alter table table_name
modify column column_name varchar(22);

alter table user_employee_info
modify column en_title varchar(1024) comment '英文title',
modify column cn_title varchar(1024) comment '中文title';

modify column 修改列注释

alter table table_name
MODIFY column column_name tinyint default '0' not null comment '用户身份, 0未知, 1非车主, 2意向金车主, 3定金车主, 4共同车主, 5正式车主'

modify column 修改列为大小写敏感的

修改表字段属性为大小写敏感的，即把列的字符序改为大小写敏感的字符序

ALTER TABLE table_name
MODIFY COLUMN code varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin not null comment '大小写敏感的code';

修改表默认字符集和所有字符列的字符集

只修改表默认字符集

alter table page_view default charset utf8;

修改表默认字符集 同时 把所有字符列（CHAR,VARCHAR,TEXT）改为新的字符集

alter table page_view convert to charset utf8;
-- 同时修改字符序
alter table page_view convert to charset utf8 COLLATE utf8_general_ci;

auto_increment 修改自增ID

alter table table_name auto_increment=73;

---

rename table 表重命名

RENAME TABLE old_table TO new_table;
等价于
ALTER TABLE old_table RENAME new_table;

13.1.33 RENAME TABLE Statement
https://dev.mysql.com/doc/refman/5.7/en/rename-table.html

---

修改数据库名(数据库重命名)

Mysql 没有直接更改数据库名称的命令。

RENAME DATABASE 这条命令在 MySQL 5.1.7 中被加入，但很快就发现这条命令所带来的危险，于是在 MySQL 5.1.23 中这条命令被移除。

可通过 mysqldump 导入导出来复制数据库，数据量大时谨慎使用
在数据库外执行以下命令，将 db_old 改名为 db_new

mysqladmin -uroot -ppswd create db_new
mysqldump -uroot -ppswd db_old | mysql -uroot -ppswd db_new

之后选择删除 db_old

---

唯一约束

唯一键约束，可以在一个列上添加约束，也可以在多个列上添加唯一约束。

在一列上添加唯一约束，主要是让该列在表中只能有唯一的一行，例如注册邮箱时的邮箱名、手机号等信息

创建表时添加唯一约束

在username列上添加唯一约束

CREATE TABLE `t_user` (
    `Id` int(11) NOT NULL AUTO_INCREMENT,
    `username` varchar(18) NOT NULL unique,
    `password` varchar(18) NOT NULL,
    PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1018 DEFAULT CHARSET=gbk;

在多列上添加唯一约束

CREATE TABLE `jw_resource` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `resource_name` VARCHAR(128) DEFAULT NULL,
  `resource_type` TINYINT(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `resource_name` (`resource_name`,`resource_type`)
) ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=gbk;

在已有表上添加唯一约束

ALTER TABLE `t_user` ADD unique(`username`);

或者：

create unique index UserNameIndex on 't_user' ('username');

在多列上添加唯一约束：

ALTER TABLE table_name
ADD UNIQUE KEY index_name(resource_name, resource_type);

查看唯一约束

show index from table_name;

修改唯一约束

必须先删除唯一约束，再添加唯一约束

删除唯一约束

注意唯一约束用index关键字，因为唯一约束其实就是索引

ALTER TABLE table_name DROP INDEX index_name;

MySQL中添加唯一约束和联合唯一约束
https://blog.csdn.net/yumushui/article/details/38960619

---

外键约束

可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后也可以（通过 ALTER TABLE 语句）

一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。
FOREIGN KEY 约束用于预防破坏表之间连接的动作。
FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
FOREIGN KEY 约束可定义在列级，也可以定义在表级。
CONSTRAINT关键字可为约束命名。
ON DELETE CASCADE 声明级联删除
ON UPDATE CASCADE 声明级联更新

定义表时添加外键约束

下面是postgresql中的一个建表语句：

CREATE TABLE "ais"."re_ais_opencloseclass_agent" (
    "agent_id" int4 NOT NULL,
    "airline" varchar(2) COLLATE "default" NOT NULL,
    "savetime" int8 NOT NULL,
    "saveuser" text COLLATE "default" NOT NULL,
    CONSTRAINT "idx_re_ais_opencloseclass_agent_pk" PRIMARY KEY ("agent_id"),
    CONSTRAINT "re_ais_opencloseclass_agent_agent_id_fkey1" FOREIGN KEY ("agent_id") REFERENCES "ais"."re_ais_base_agent" ("agent_id") ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT "re_ais_opencloseclass_agent_agent_id_airline_key" UNIQUE ("agent_id", "airline")
)

在现有表上添加外键约束

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)

或者：

alter table orders
add FOREIGN KEY user_id_fk(user_id) REFERENCES user(id);

修改约束

若要修改主键约束、外键约束、唯一约束、检查约束，必须先删除现有的约束，然后再用新定义重新创建该约束。

删除外键约束

ALTER TABLE table_name
DROP FOREIGN KEY foreign_key_name[1,...n]

Can’t write; duplicate key in table xxx

错误：
使用 Jpa 的 SpringBoot 服务启动时报错：

Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: Unable to execute schema management to JDBC target [alter table common_user_role add constraint FKjui11rhjxr1u2hd6un2fdggkp foreign key (role_id) references common_role (id)]
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Can't write; duplicate key in table '#sql-1_42613'

由于设置了 hibernate.hbm2ddl.auto=update 启动时执行了 alter 语句，添加一个外键约束时失败了。

原因：
数据库中已有一个同名约束，即约束的名字 FKjui11rhjxr1u2hd6un2fdggkp 重复了。
MySQL 约束名在整个数据库中必须是唯一的，只在一个表上是唯一的还不行。
也就是说，假如同数据库的表 tableA 上有名字是 constraintA 的约束，那么 tableB 上再创建名为 constraintA 的约束就会失败。

在 information_schema 库的 KEY_COLUMN_USAGE 表中可以查看某个约束名在数据库上是否已经存在：

SELECT TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE CONSTRAINT_NAME = 'FKjui11rhjxr1u2hd6un2fdggkp';

解决：
果然同一个库上有个测试表上已有名字是 FKjui11rhjxr1u2hd6un2fdggkp 的约束，手动 drop 表后解决。

---

mysql外键

在MySQL 3.23版本后，InnoDB引擎类型的表支持了外键约束，外键主要用于引用和参照完整性的约束检查。外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作。但是外键的使用是有一些必要条件的：

1. 两个表必须是InnoDB表，MyISAM表暂时不支持外键（据说以后的版本有可能支持，但至少目前不支持）；

2. 外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立；

3. 外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；

在InnoDB存储引擎中，对于一个外键列，如果没有显示地对这个列加索引，InnoDB存储引擎会自动对其加一个索引，因为这样可以避免表锁。这比Oracle数据库做得好，Oracle数据库不会自动添加索引，用户必须自己手动添加，这也导致了Oracle数据库中可能产生死锁。

外键的定义语法：

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

该语法可以在CREATE TABLE和ALTER TABLE时使用，如果不指定CONSTRAINT symbol，MySQL会自动生成一个名字。对于ON DELETE、ON UPDATE表示事件触发限制。

一般来说，称被引用的表为父表，引用的表为子表。外键定义时ON DELETE和ON UPDATE表示在对父表进行DELETE或UPDATE操作时，对子表所做的操作，可定义的子表操作有：

CASCADE（跟随外键改动）：表示当父表发送DELETE或UPDATE操作时，对相应的子表中的数据也进行DELETE和UPDATE操作。
SET NULL（设空值）：表示当父表发送DELETE或UPDATE操作时，对相应的子表中的数据更新为NULL值，但子表对应的列必须允许为NULL。
SET DEFAULT（设默认值）：表示当父表发送DELETE或UPDATE操作时，对相应的子表中的数据更新为默认值。
NO ACTION（无动作，默认的）：表示当父表发送DELETE或UPDATE操作时，抛出错误，不允许这类操作发生。
RESTRICT（限制外表中的外键改动）：表示当父表发送DELETE或UPDATE操作时，抛出错误，不允许这类操作发生。如果定义外键时没有指定ON DELETE或ON UPDATE，RESTRICT就是默认的外键设置。

在其他数据库中，如Oracle数据库中，有一种称为延时检查（deferred check）的外键约束，即检查在SQL语句运行完成后再进行。而目前MySQL数据库的外键约束都是即时检查，因此从上面的定义可以看出，在MySQL数据库中NO ACTION和RESTRICT的功能是相同的。

对于参照完整性约束，外键能起到一个非常好的作用。但是对于数据的导入操作时，外键往往导致在外键约束的检查上花费大量时间。因此MySQL数据库的外键是即时检查的，所以对导入的每一行都会进行外键检查。但是用户可以在导入过程中忽视外键的检查，如：

mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA ....

mysql> set foreign_key_checks=1;
Query OK, 0 rows affected (0.00 sec)

对于外键值的插入或更新，首先需要检查父表中的记录，既SELECT父表。但是对于父表的SELECT操作，不是使用一致性非锁定读的方式，因为这会发生数据不一致的问题，因此这时使用的是SELECT…LOCK IN SHARE MODE方式，即主动对父表加一个S锁。如果这时父表上已经这样加X锁，子表上的操作会被阻塞，如下：

先创建测试环境，如下：

# 创建parent表;
create table parent(
  tag_id int primary key auto_increment not null,
  tag_name varchar(20)
);

# 创建child表;
create table child(
  article_id int primary key auto_increment not null,
  article_tag int(11),
  CONSTRAINT  tag_at FOREIGN KEY (article_tag) REFERENCES parent(tag_id)
);

# 插入数据;
insert into parent(tag_name) values('mysql');
insert into parent(tag_name) values('oracle');
insert into parent(tag_name) values('mariadb');

开始测试

# Session A
mysql> begin
mysql> delete from parent where tag_id = 3;

# Session B
mysql> begin
mysql> insert into child(article_id, article_tag) values(1,3);   #阻塞

Session B 执行insert语句时被阻塞。当Session A手动执行commit提交事务后，Session B才能继续。

在上述的例子中，两个会话中的事务都没有进行COMMIT或ROLLBACK操作，而会话B的操作会被阻塞。这是因为tag_id为3的父表在会话中已经加了一个X锁，而此时在会话B中用户又需要对父表中tag_id为3的行加一个S锁，这时INSERT的操作会被阻塞。设想如果访问父表时，使用的是一致性的非锁定读，这时Session B会读到父表有tag_id=3的记录，可以进行插入操作。但是如果会话A对事务提交了，则父表中就不存在tag_id为3的记录。数据在父、子表就会存在不一致的情况。

MySQL InnoDB外键约束详解 - 运维那点儿事 质量很高的文章
http://www.ywnds.com/?p=9135

---

外键与锁

在mysql的多个存储引擎中，innodb支持外键，但是由于外键，也会对innodb表增加锁定机制；

所有的外键相关的操作都在数据更改时，比如检查数据完整性、增加锁定等；

假设一个表为 parent ，一个表为child，child表有外键pid REFERENCES parent(id)；在一个session中set autocommit=0，执行对parent或child的操作，在另一个
session执行对child或parent的操作，从而得出以下结论：

一、对父表的操作
1、insert to parent ，新插入行的id值为XXX
child会锁外键值为XXX的行,不会锁其他行

2、update parent ，原id为XXX，现id为YYY
child会锁外键值为XXX,YYY的行,不会锁其他行

3、delete from parent ，删除行的id为XXX
child会锁外键值为XXX,不会锁其他行

二、对子表的操作
1、insert to child，插入行的外键值为XXX
parent 会锁值XXX的行,不会锁其他行

2、update child，更新行的外键值原为XXX，现为YYY
parent 会锁XXX,YYY行,但要注意:存在间隔锁,也会锁其他行(XXX,YYY之间的位置)

3、delete from child，删除行的外键值为XXX
parent 会锁XXX的行,但要注意:存在间隔锁,也会锁其他行(XXX-1的位置)

mysql 外键锁机制
https://blog.csdn.net/lan12334321234/article/details/70049370

---

InnoDB会自动为外键创建索引

下面是 mysql(mariadb) 中的一个建表语句，原始sql如下：

-- 用户地址表, 常用地址和收货地址
DROP TABLE IF EXISTS user_address;
CREATE TABLE user_address (
  id               BIGINT                        NOT NULL AUTO_INCREMENT,
  app_id           INT                           NOT NULL,
  user_id          BIGINT                        NOT NULL,
  status           ENUM ('enable', 'disable')    NOT NULL DEFAULT 'enable'
  COMMENT '状态',
  province_id      BIGINT
  COMMENT '省份id',
  province         VARCHAR(64)
  COMMENT '省份',
  city_id          BIGINT
  COMMENT '城市id',
  city             VARCHAR(64)
  COMMENT '城市',
  region_id        BIGINT
  COMMENT '区县id',
  region           VARCHAR(64)
  COMMENT '区县',
  detailed_address VARCHAR(512)                  NOT NULL
  COMMENT '详细地址',
  type             ENUM ('usual', 'deliverable') NOT NULL
  COMMENT 'usual:常用联系地址，deliverable:收货地址',
  priority         INT                           NOT NULL DEFAULT 1
  COMMENT '序号，用于排序',
  is_default       BOOLEAN                       NOT NULL DEFAULT FALSE
  COMMENT '是否默认地址',
  ext_data         VARCHAR(1024)                 NOT NULL
  COMMENT '姓名、手机号地区码、手机号、别名,json字符串',
  `create_time`    DATETIME                      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time`    TIMESTAMP                     NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES user (id),
  FOREIGN KEY (province_id) REFERENCES china_region (id),
  FOREIGN KEY (city_id) REFERENCES china_region (id),
  FOREIGN KEY (region_id) REFERENCES china_region (id)
)
  ENGINE = InnoDB,
  DEFAULT CHARSET = utf8mb4;

创建完后在 DataGrip 中查看建表语句的话，发现多了几个外键的创建索引语句，这是mysql自动给加外键的索引

create table user_address
(
    id bigint auto_increment primary key,
    user_id bigint not null,
    province_id bigint null comment '省份id',
    province varchar(64) null comment '省份',
    city_id bigint null comment '城市id',
    city varchar(64) null comment '城市',
    region_id bigint null comment '区县id',
    region varchar(64) null comment '区县',
    detailed_address varchar(512) not null comment '详细地址',
    type enum('usual', 'deliverable') not null comment 'usual:常用联系地址，deliverable:收货地址',
    priority int default '1' not null comment '序号，用于排序',
    is_default tinyint(1) default '0' not null,
    ext_data varchar(1024) null comment '姓名、手机号地区码、手机号、别名,json字符串',
    app_id int not null,
    status enum('enable', 'disable') default 'enable' not null comment '状态',
    create_time datetime default CURRENT_TIMESTAMP not null,
    update_time timestamp default CURRENT_TIMESTAMP not null,
    constraint user_address_ibfk_1
        foreign key (user_id) references user (id),
    constraint user_address_ibfk_2
        foreign key (province_id) references china_region (id),
    constraint user_address_ibfk_3
        foreign key (city_id) references china_region (id),
    constraint user_address_ibfk_4
        foreign key (region_id) references china_region (id)
);

-- 下面4个创建索引的语句是mysql自动加的
create index user_id on user_address (user_id);
create index city_id on user_address (city_id);
create index province_id on user_address (province_id);
create index region_id on user_address (region_id);

直接在命令行用 show create table user_address 看建表sql也会看到多了几个建索引的 KEY 语句：

CREATE TABLE `user_address` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `province_id` bigint(20) DEFAULT NULL COMMENT '省份id',
  `province` varchar(64) DEFAULT NULL COMMENT '省份',
  `city_id` bigint(20) DEFAULT NULL COMMENT '城市id',
  `city` varchar(64) DEFAULT NULL COMMENT '城市',
  `region_id` bigint(20) DEFAULT NULL COMMENT '区县id',
  `region` varchar(64) DEFAULT NULL COMMENT '区县',
  `detailed_address` varchar(512) NOT NULL COMMENT '详细地址',
  `type` enum('usual','deliverable') NOT NULL COMMENT 'usual:常用联系地址，deliverable:收货地址',
  `priority` int(11) NOT NULL DEFAULT '1' COMMENT '序号，用于排序',
  `is_default` tinyint(1) NOT NULL DEFAULT '0',
  `ext_data` varchar(1024) DEFAULT NULL COMMENT '姓名、手机号地区码、手机号、别名,json字符串',
  `app_id` int(11) NOT NULL,
  `status` enum('enable','disable') NOT NULL DEFAULT 'enable' COMMENT '状态',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `province_id` (`province_id`),
  KEY `city_id` (`city_id`),
  KEY `region_id` (`region_id`),
  CONSTRAINT `user_address_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
  CONSTRAINT `user_address_ibfk_2` FOREIGN KEY (`province_id`) REFERENCES `china_region` (`id`),
  CONSTRAINT `user_address_ibfk_3` FOREIGN KEY (`city_id`) REFERENCES `china_region` (`id`),
  CONSTRAINT `user_address_ibfk_4` FOREIGN KEY (`region_id`) REFERENCES `china_region` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10070 DEFAULT CHARSET=utf8mb4

show index 查看索引如下：

MariaDB [uds]> show index from user_address;
+--------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user_address |          0 | PRIMARY     |            1 | id          | A         |        2807 |     NULL | NULL   |      | BTREE      |         |               |
| user_address |          1 | user_id     |            1 | user_id     | A         |        2807 |     NULL | NULL   |      | BTREE      |         |               |
| user_address |          1 | province_id |            1 | province_id | A         |          56 |     NULL | NULL   | YES  | BTREE      |         |               |
| user_address |          1 | city_id     |            1 | city_id     | A         |         140 |     NULL | NULL   | YES  | BTREE      |         |               |
| user_address |          1 | region_id   |            1 | region_id   | A         |         255 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.016 sec)

---