1、SQL种类
2、DDL_库定义
工作中删除类的SQL, 加上#注释, 选中运行, 防止误删除
- 2.4、DDL_库定义规范
- 2.4、查库
3、DDL_表定义
- 3.1、DDL_建表
- 3.1.1、表定义规范
3.2、查看表
- 3.2.1、SQL审核工具
3.3、修改表
4、DDL 工具
- 4.1、online DDL(了解)
- 4.2、DDL 工具

1、SQL种类

DDL、DML、DQL、DCL
对应英文全称: Data (Definition、Manipulation、Query、Control) Language
DDL: 数据定义语言
操作对象是库、表; 创建、修改、删除
DML: 数据操作语言
操作对象是记录; 插入、删除、修改
DQL: 数据查询语言
操作对象是记录; 查询
DCL: 数据控制语言
主要是用来设置或更改数据库用户或角色权限的语句
grant、revoke

2、DDL_库定义

2.1、创建数据库

#语法
CREATE DATABASE 数据库名 CHARACTER SET 字符集 COLLATE 校对规则;
#创建数据库db01,字符集utf8mb4,校对规则utf8mb4_general_ci
create database db01 character set utf8mb4 collate utf8mb4_general_ci;
#8.0版本之前默认字符集为latin1,不能存中文,一定要设置字符集

2.2、修改数据库

#语法
alter database 数据库名 character set 字符集 校对规则;

#修改数据库不能修改数据库名称,只能修改字符集
create database testdb character set utf8 collate utf8_general_ci;
show create database testdb;

alter database testdb character set utf8mb4 collate utf8mb4_general_ci;
show create database testdb;

2.2.3、设置默认字符集

#客户端设置临时字符集
set names utf8mb4;

#服务器端设置配置默认字符集
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
skip-character-set-client-handshake
#忽略应用连接自己设置的字符编码，保持与全局设置一致

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

#[client]、[mysql] 设置的是命令行工具的默认字符,对开发工具不能生效
用skip-character-set-client-handshake保持字符一致性

2.2.4、字符集注意事项

参考链接
https://mp.weixin.qq.com/s/budb7JibT0wzPQYVyqvY4w

1. 修改字符集时,会加锁,写入很慢
2. 生产中,服务器端、客户端、开发工具、api、应保持一致,防止字符乱码

2.3、删库

工作中删除类的SQL, 加上#注释, 选中运行, 防止误删除

#drop database 库名;
#drop database db01;

2.4、DDL_库定义规范

1. 库名不能数字开头
2. 库名要和业务有关
3. 库名不要有大写字符
   原因：为了多平台兼容
4. 建库需要显示指定字符集,建议是utf8mb4
5. 生产中禁用普通用户的drop database权限

2.4、查库

#查看建库语句
show create database 数据库名;
show create database testdb;

#查看所有库
show databases;

3、DDL_表定义

DDL所有操作都会进行锁表, 必须避开业务繁忙执行, 避免锁等待、锁争用

3.1、DDL_建表

#语法
CREATE TABLE table_name (column_name column_type);

#示例:在testdb库创建student表
create table testdb.student
( 
`id` int NOT NULL AUTO_INCREMENT COMMENT '学号',
 `name` varchar(64) NOT NULL COMMENT '学生姓名',
 `age` tinyint UNSIGNED NOT NULL DEFAULT 0 COMMENT '学生年龄',
 `gender` CHAR(1) NOT NULL DEFAULT 'n' COMMENT '学生性别',
 `address` enum('北京','天津','上海','重庆','河北','河南','山东','未知') NOT NULL DEFAULT '未知' COMMENT '省份', 
 `intime` datetime NOT NULL COMMENT '入学时间',
 `shenfen` CHAR(18) NOT NULL COMMENT '身份证',
 `num` int(10) UNSIGNED ZEROFILL NOT NULL COMMENT '测试', 
 primary key (`id`) 
) ENGINE=InnoDB charset=utf8mb4 collate=utf8mb4_bin; 

#克隆表结构,创建和源表结构一致的表,不会插入数据
create table 克隆表名 like 源表名;
create table student_clone like student;

#补充
``:反引号,去除关键字含义,解析成普通字符

#建表规范
  a. 表名 
    不能数字开头
    业务有关
    不要大写字母
    不要超过18字符
    不能是关键字
  b. 存储引擎使用InnoDB
  c. 5.7版本以后,字符集使用utf8mb4
  d. 列名要和业务有关,不要超过15个字符,最大不超过20个字符
  e. 选择合适、足够、简短数据类型
  f. 建议每个列设置not null
  g. 每个列要有注释
  h. 每个表要有主键
  i. 针对not null 列，可以设定默认值。
  j. 表注释

3.1.1、表定义规范

1. 建表  
    a. 表名
        不能数字开头
        业务有关
        不要大写字母
        不要超过18字符
        不能是关键字
    b. 存储引擎使用InnoDB
    c. 5.7版本以后,字符集使用utf8mb4
    d. 列名要和业务有关,不要超过15个字符
    e. 选择合适、足够、简短数据类型
    f. 建议每个列设置not null
    g. 每个列要有注释
    h. 每个表要有主键
    i. 针对not null 列,可以设定默认值
    j. 表注释    
2. 修改表 
    a. 添加列,使用追加式添加列 
    b. 修改列属性,尽量使用modify语句
    c. 修改表定义,建议在业务不繁忙期间进行,尽量采用pt-osc或者gh-ost工具减少业务影响

3.2、查看表

#查看表
use testdb;
show tables;

#查看建表语句
show create table 库.表;
show create table student;

3.2.1、SQL审核工具

Yearning
项目地址
https://github.com/cookieY/Yearning
项目简介
https://guide.yearning.io/
Archery
项目地址
https://github.com/hhyo/Archery
使用文档
https://archerydms.com/

3.3、修改表

生产环境中, DDL修改操作风险比较高, 需要运维 (DBA) 人员介入

3.3.1、添加字段

#显示表结构
desc student;

#语法(新字段默认会添加在最后一列)
alter table 表名
add column 字段名
数据类型 约束 comment '注释';

#更改student表结构,添加phone字段
alter table testdb.student
add column phone
varchar(11) not null default 00000000000 comment '手机号';

不推荐的方式

#指定位置添加字段会造成更严重的锁表时间

#添加新字段在某个字段之后
alter table 表名
add column 字段名
数据类型 约束 comment '注释' after 字段名;

#添加新字段在第一列
alter table 表名
add column 字段名
数据类型 约束 comment '注释' first 字段名;

3.3.2、删除字段

字段里的记录会被删除

alter table 表名
drop column 字段名;

3.3.3、修改表名

alter table 表名 rename to 新表名;
alter table testdb.student rename to stu;

3.3.4、修改存储引擎

#修改tb01表存储引擎myisam 为 innodb
create table tb01 (id int(11)) engine=myisam;
show create table tb01;

alter table tb01 engine=innodb;
show create table tb01;

3.3.5、修改字符集

create table tb02(id int(11))charset=utf8;
show create table tb02;

alter table tb02 charset=utf8mb4;
show create table tb02;

3.3.6、修改字段名

#使用change修改表名时,需要加上旧字段的属性信息
alter table 表名 
change 旧字段名 新字段名 旧字段约束;

#修改字段名shenfen为cardnum
alter table stu
change shenfen cardnum CHAR(18) not null default '0' comment '身份证';

3.3.7、修改默认值

#修改cardnum默认值为1
alter table stu 
change cardnum cardnum CHAR(18) not null default '1' comment '身份证';

3.3.8、修改字段数据类型

#使用modify修改时,也要加上原有属性信息
alter table stu
modify cardnum CHAR(20) not null default '1' comment '身份证';

3.3.9、删除表

#drop table 表名;
#drop table stu;

4、DDL 工具

4.1、online DDL(了解)

4.1.1、什么是 online DDL

参考链接
https://www.cnblogs.com/mysql-dba/p/6192897.html
online DDL是在mysql 5.6版本后加入的特性, 用于支持DDL执行期间DML语句的并行操作, 提高数据库的吞吐量

4.1.2、各个版本 online DDL有什么区别

5.7 online DDL参考文档
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.html
8.0 online DDL参考文档
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-column-operations

4.1.3、online DDL算法

4.1.3.1、copy (offline)

在DDL执行期间不能与DML语句并行, 是5.6版本前的DDL执行方法, 会生成 (临时)表, 将原数据逐行拷贝到新表中, 在此期间会阻塞DML语句

4.1.3.2、inplace

原地直接修改重建新表, 不会拷贝全表数据到新表. 在DDL的初始准备和最后结束两个阶段时, 通常需要加DML排他锁 (metadata lock, 元数据锁), 除此外, DDL期间不会阻塞DML语句

4.1.3.3、instant

只需修改数据字典中的元数据, 无需拷贝数据也无需重建整表, 同样: 也无需加排他MDL锁, 原表数据也不受影响
整个DDL过程几乎是瞬间完成的, 也不会阻塞DML. 这个新特性是8.0.12引入的, 再次感谢腾讯互娱DBA团队的贡献
instant方式支持的操作: 修改表名、追加方式加字段, 虚拟列

4.1.3.4、online DDL总结

(1). 在alter table 时, 虽然可以使用algorithm=DDL算法, 但是无需手工指定触发, mysql会自动选择合适的算法
(2). 官方虽然提供了online DDL, 但是执行DDL操作时, 必须避开业务繁忙时进行

4.2、DDL 工具

4.2.1、推荐工具

推荐工具: pt-osc、gh-ost
pt-osc工具参考地址
https://www.jianshu.com/p/c97228b6f60c

4.2.2、工作原理

使用pt-osc工具执行DDL操作时, 可以正常执行DML操作, 不阻塞. 不会减少DDL执行的时间 (alter table …)

4.2.3、执行流程

1.查看是否有从节点
2.查看是否有外键
3.创建新表
4.修改新表结构
5.创建触发器，保证拷贝过程中的数据同步
6.拷贝表数据
7.rename表
8.删除旧表
9.删除触发器**

MySQL_DBA

MySQL-DDL语句