优化SQL语句

基于设计工具生成的SQL语句可能会有不足，例如按照【 使用Enterprise Architect设计数据库】所述方法，生成的SQL存在一些问题。如下一一列举：

1. 语句间的分隔统一风格

USE `db_jygt`
;

使用notepad++可以很方便处理这类文本。使用notepad++打开sql文件，选中要替换的内容，按Ctrl+h键，在弹出的对话框输入要换成的内容，点击全部替换即可。

处理之后的SQL语句如下所示：

USE `db_jygt`;

SET FOREIGN_KEY_CHECKS=0;
/* Drop Tables */

DROP TABLE IF EXISTS `t_button` CASCADE;

2. 去掉Delete表时，MySQL不支持的CASCADE

在MySQL中，如下语句中的CASCADE并没有什么作用。应该去掉。

DROP TABLE IF EXISTS `t_button` CASCADE;

使用notepad++的正则表达式替换可以很简单完成此类语句的替换。

查找匹配的字符串 ： 例如 DROP TABLE IF EXISTS `t_button` CASCADE;
^DROP([ A-Z`a-z_]+)CASCADE;$

将其中的CASCAD去掉
DROP$1;

具体如图：

处理之后的SQL语句如下所示：

USE `db_jygt`;

SET FOREIGN_KEY_CHECKS=0;
/* Drop Tables */

DROP TABLE IF EXISTS `t_button` ;

DROP TABLE IF EXISTS `t_element` ;

DROP TABLE IF EXISTS `t_field` ;

DROP TABLE IF EXISTS `t_field_content` ;

DROP TABLE IF EXISTS `t_group` ;

3. 使用 `ENGINE` 和 `CHARSET`

EA创建的表，一般没有指定引擎和字符集。应该结合业务需要予以明确。MySQL提供的存储引擎有多种（InnoDB：支持读写高并发与事务；MyISM:高的读性能；还有MEMORR、CSV、ARCHIVE、FEDERATED、MDB、LBLACKHOLE支持各个不同的场景）。而字符集直接影响了数据库对数据的存储、比较和排序规则，并可以设置数据库级别、表级别和列基本的字符集和排序规则。字符集这里选择utf8mb4，并按照utf8mb4_bin规则排序。

### 1. 字符集（Character Set）

字符集是一个字符的集合，每个字符都有一个对应的编码。MySQL 支持多种字符集，主要包括：

- **ASCII**：美国标准信息交换码，包含128个字符（从0到127），主要用于英文字符。
- **UTF-8**：一种可变长度的字符编码，可以表示所有 Unicode 字符，广泛用于多语言支持。
- **UTF-16**：另一种 Unicode 编码方式，使用2或4个字节表示字符。
- **latin1**：ISO 8859-1 编码，支持西欧语言的字符。
- **utf8mb4**：UTF-8 的扩展版本，支持所有 Unicode 字符，包括表情符号。

#### 常见字符集的选择：
- **utf8mb4**：推荐用于需要多语言支持和表情符号的应用。
- **latin1**：适用于仅需要支持西欧语言的应用。

### 2. 排序规则（Collation）

排序规则定义了如何比较和排序字符。每个字符集可以有一个或多个排序规则。排序规则决定了以下行为：

- 字符的比较：例如，'a' 和 'A' 是否被视为相等。
- 字符的排序顺序：例如，字母的顺序是按字母表排序还是按其他规则排序。

#### 常见排序规则：
- **utf8mb4_general_ci**：不区分大小写，速度较快，适用于大多数情况。
- **utf8mb4_unicode_ci**：遵循 Unicode 标准，支持多语言比较，准确性高，但速度较慢。
- **utf8mb4_bin**：区分大小写，按字节比较，适合需要精确比较的场景。
### 3. 设置字符集和排序规则

#### 3.1. 数据库级别
可以在创建数据库时指定字符集和排序规则：
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

#### 3.2. 表级别
在创建表时也可以指定字符集和排序规则：
CREATE TABLE mytable (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

#### 3.3. 列级别
在定义列时，可以为特定列设置字符集和排序规则：
CREATE TABLE mytable (
    id INT PRIMARY KEY,
    name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);
### 4. 查看当前字符集和排序规则
可以使用以下 SQL 查询查看当前数据库、表和列的字符集和排序规则：

- 查看数据库字符集和排序规则：
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'mydb';

- 查看表字符集和排序规则：
SHOW TABLE STATUS LIKE 'mytable';

- 查看列字符集和排序规则：
SHOW FULL COLUMNS FROM mytable;

### 5. 变更字符集和排序规则
如果需要更改现有数据库、表或列的字符集和排序规则，可以使用 `ALTER` 语句：

  - 修改数据库：
ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

- 修改表：
ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

- 修改列：
ALTER TABLE mytable MODIFY name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

EA生成的SQL创建的表，并没有指定字符集，如下

CREATE TABLE `t_button`
(
	`f_id` INT NOT NULL AUTO_INCREMENT COMMENT '主键：唯一标识特定表的一个记录',
	`f_name` VARCHAR(64) NOT NULL COMMENT '菜单的名称',
	`f_group_id` INT NULL COMMENT '分组id',
	`f_label` VARCHAR(64) NULL COMMENT '显示的名称',
	`f_icon` VARCHAR(128) NULL COMMENT '显示的图标',
	`f_action` VARCHAR(128) NULL COMMENT '点击后的动作',
	`f_paras` VARCHAR(512) NULL COMMENT '点击后action携带的参数',
	`f_desc` VARCHAR(512) NULL COMMENT '资源的描述',
	`f_state` INT NOT NULL DEFAULT 0 COMMENT '状态（可用1/不可用0）',
	`f_create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`f_modify_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `PK_t_button` PRIMARY KEY (`f_id` ASC)
)
;

4. 外键设置合理的动作

在 MySQL 中，定义外键约束时，可以选择不同的行为来处理当引用的数据被删除或更新时的情况。以下是 `ON DELETE CASCADE`、`ON DELETE SET NULL`、`ON DELETE RESTRICT` 和 `ON UPDATE RESTRICT` 的详细比较：

 1. `ON DELETE CASCADE`
- **行为**：当父表（被引用的表）中的一行被删除时，所有在子表（包含外键的表）中与之对应的行也会被自动删除。
- **使用场景**：适用于需要确保相关数据自动删除以维护引用完整性的情况。例如，在订单系统中，如果删除一个订单，应该自动删除与该订单相关的所有订单项。

### 2. `ON DELETE SET NULL`
- **行为**：当父表中的一行被删除时，子表中所有对应的外键列将被设置为 `NULL`。
- **使用场景**：适用于希望保留子记录但移除与已删除父记录的关联的情况。例如，在用户与帖子之间的关系中，如果删除一个用户，可以将帖子表中的 `author_id` 设置为 `NULL`，表示该帖子没有关联的作者。

### 3. `ON DELETE RESTRICT`
- **行为**：如果子表中有任何对应的行，则不允许删除父表中的行。也就是说，只有在没有子记录引用的情况下，才能删除父记录。
- **使用场景**：适用于需要确保在删除父记录时不会留下孤立子记录的情况。这有助于维护数据完整性，避免出现无效的引用。

### 4. `ON UPDATE RESTRICT`
- **行为**：类似于 `ON DELETE RESTRICT`，如果子表中有任何对应的行，则不允许更新父表中的行。如果试图更新被子记录引用的父记录，更新将被拒绝。
- **使用场景**：适用于希望确保父记录的主键在仍被子记录引用时不能被更改的情况。

这些不同，一般可以在EA设计时选定，在生成SQL之后要仔细检查。除此之外，还有ON ACTION，其与RESTRICT规则基本相似，只是其检查的时机不同，适合事务中多条语句执行时确保数据完整性，并增加成功率。如下：

在 MySQL 中，`NO ACTION` 和 `RESTRICT` 都是用于外键约束的选项，用来定义当尝试删除或更新父表中被子表引用的行时，数据库应该如何处理。虽然这两个选项在很多情况 下表现得很相似，但它们之间还是有一些细微的差别：

### 1. `NO ACTION`
- **行为**：当你指定 `ON DELETE NO ACTION` 或 `ON UPDATE NO ACTION` 时，如果试图删除或更新父表中的一行，而该行在子表中仍然被引用，那么该操作将被拒绝。值得注意的是，这种检查是在 SQL 语句的末尾进行的。
- **使用场景**：这在复杂事务中非常有用，例如当你在一个事务中执行多个操作时。如果所有操作在事务结束时都有效且没有违反引用完整性，则这些更改将被允许。

### 2. `RESTRICT`
- **行为**：当你指定 `ON DELETE RESTRICT` 或 `ON UPDATE RESTRICT` 时，如果试图删除或更新父表中的一行，而该行在子表中仍然被引用，该操作也会被拒绝。不过，这种检查是在操作尝试时立即进行的，而不是在语句的末尾。
- **使用场景**：这种方式更直接，通常用于简单的操作中，确保父记录在仍然被引用时不能被删除或更新。

### 关键区别
- **检查时机**：
  - `NO ACTION`：检查在事务结束时进行。如果所有操作有效，最终的更改可以被提交。
  - `RESTRICT`：检查是立即进行的，如果有任何违反引用完整性的情况，操作会被立即拒绝。

### 总结
在实践中，许多数据库系统，包括 MySQL，通常将 `NO ACTION` 和 `RESTRICT` 视为相似，尤其是在简单操作中。然而，关键的区别在于引用完整性检查的时机。

- 使用 `NO ACTION` 当你希望允许一系列操作，这些操作在事务结束时可能会暂时违反引用完整性，但最终会得到解决。
- 使用 `RESTRICT` 当你希望立即执行引用完整性检查，拒绝任何会违反完整性的操作。

参考资料：

1. 使用Enterprise Architect设计数据库
2. gitee仓库 ：https://gitee.com/wapuboy/ealearning.git

Tags：show columns