Java开发经验——数据库开发经验
摘要
本文主要介绍了Java开发中的数据库操作规范，包括数据库建表规范、索引规约、SQL规范和ORM规约。强调了在数据库设计和操作中应遵循的最佳实践，如字段命名、数据类型选择、索引创建、SQL语句编写和ORM映射，旨在提高数据库操作的性能和安全性。
1. 数据库建表规范
【强制】表达是与否概念的字段，必须使用is_xxx的方式命名，数据类型的是unsigned tinyint(1表示是，0表示否)。说明：任何字段如果为非负数，必须是unsigned。
数据类型的语义约束
- 明确非负性：对于明确只接受非负数的字段，将其定义为
unsigned可以从数据库层面明确约束该字段不能存储负值。例如，age或is_xxx这种字段不应出现负值，使用unsigned提高了数据约束能力。 - 防止错误数据：若定义为有符号类型（
signed），可能因代码或外部输入错误而导致负数插入，而unsigned则能直接避免此类问题。
节省存储空间
TINYINT是 1 字节的类型，取值范围：
signed：-128到127unsigned：0到255
在明确字段非负的情况下，使用 unsigned 可以充分利用数值范围。例如：布尔型的 TINYINT(1) 只需要 0 和 1 两个值，使用 unsigned 更贴合业务需求，且减少浪费。
性能优化
- 索引和比较效率更高：数据库在处理
unsigned数据时会省略对负值的检查和处理逻辑，能够略微提升查询和比较的效率。 - 存储和传输效率：尽管单个字段的差异可能不明显，但在高并发或大数据量场景下，
unsigned的节省会带来整体性能优化。
注意：POJO类中的任何布尔类型的变量，都不要加is前缀，所以，需要在<resultMap>设置从isx到Xx的映射关系。数据库表示是与否的值，使用tinyint类型，坚持isxx的命名方式是为了明确其取值含义与取值范围。
正例：表达逻辑删除的字段名is deleted,1表示删除，0表示未删除。
CREATE TABLE credit_risk_check (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id INT UNSIGNED NOT NULL, -- 用户IDis_blacklisted TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, -- 是否加入黑名单is_fraudulent TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 -- 是否疑似欺诈
);CREATE TABLE users (user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,is_active TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, -- 是否激活用户is_deleted TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 -- 是否已删除
);【强制】强制表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。
说明：MySQL在Vindows下不区分大小写，但在Liux下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任何大写字母，避免节外生枝。
正例：aliyun admin,rdc_config,level_3name反例：AliyunAdmin,rdcConfig,level_3_name【强制】表名不使用复数名词。
说明: 表名应该仅仅表示表里面的实体内容，不应该表示实体数量，对应于 DO 类名也是单数形式，符合表达习惯。
【强制】禁用保留字，如desc、range、match、delayed等，请参考MySQL官方保留字。
【强制】主键索引名为pk_字段名; 唯一索引名为uk_字段名; 普通索引名则为idx_字段名。
说明: pk_ 即 primary key;uk_ 即 unique key;idx_ 即 index 的简称。
主键索引（Primary Key Index）
特点
- 唯一性约束：主键索引保证表中每一行的主键值是唯一的，且不能为空。
- 自动创建索引：定义主键时，数据库会自动为主键字段创建一个唯一的聚簇索引（如 MySQL 的 InnoDB 引擎）。
- 聚簇索引：在支持聚簇索引的引擎中，表的数据存储会按照主键索引的顺序组织。
- 每张表仅能有一个主键。
使用场景
- 用于标识表中每一行记录的唯一标识符（例如
user_id或订单号order_id）。 - 通常设计为自增字段或全局唯一标识符（UUID）。
CREATE TABLE users (user_id INT UNSIGNED AUTO_INCREMENT,name VARCHAR(100) NOT NULL,PRIMARY KEY (user_id) -- 主键索引
);唯一索引（Unique Key Index）
特点
- 唯一性约束：唯一索引确保字段值在整个表中唯一，但允许
NULL值（多个NULL被认为是不同的）。 - 支持多个字段：一个表可以有多个唯一索引。
使用场景
- 用于约束一些字段的唯一性，例如邮箱地址、用户名或身份证号。
- 避免重复数据输入，保证数据一致性。
示例
CREATE TABLE users (email VARCHAR(255) NOT NULL,phone_number VARCHAR(15) NOT NULL,CONSTRAINT uk_email UNIQUE (email), -- 唯一索引CONSTRAINT uk_phone_number UNIQUE (phone_number) -- 唯一索引
);普通索引（Normal Index）
特点
- 无约束限制：普通索引仅用于加速查询性能，对字段的唯一性或非空没有限制。
- 支持重复值：普通索引的字段可以有重复值，也可以为
NULL。 - 支持组合索引：普通索引可以包含一个或多个字段。
使用场景
- 用于优化查询频繁的字段，特别是非唯一字段，如用户名、创建时间等。
- 用于加速排序或范围查询。
示例
CREATE TABLE users (username VARCHAR(50) NOT NULL,created_at DATETIME NOT NULL,INDEX idx_username (username), -- 普通索引INDEX idx_created_at (created_at) -- 普通索引
);| 属性 | 主键索引 | 唯一索引 | 普通索引 |
| 唯一性约束 | 是 | 是 | 否 |
| 是否允许 NULL | 否 | 是（允许多个 NULL） | 是 |
| 数量限制 | 每张表只能有一个 | 一张表可以有多个 | 一张表可以有多个 |
| 功能 | 标识每行记录的唯一性 | 限制字段值的唯一性 | 提高查询效率 |
| 是否自动创建 | 是（创建主键时自动生成） | 否（需手动创建） | 否（需手动创建） |
| 是否影响物理存储 | 是（聚簇索引在 InnoDB 中会影响存储） | 否 | 否 |
| 典型使用场景 | 自增 ID、全局唯一标识符 | 邮箱、身份证号、用户名 | 高查询频率的普通字段 |
【强制】 小数类型为decimal，禁止使用float和double。
说明: 在存储的时候，float 和 double 都存在精度损失的问题，很可能在比较值的时候，得到不正确的 结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数并分开存储。或者使用使用字符类型的存储。
CREATE TABLE users (user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- 主键email VARCHAR(255) NOT NULL, -- 邮箱（唯一）phone_number VARCHAR(15) NOT NULL, -- 手机号（唯一）username VARCHAR(50) NOT NULL, -- 用户名created_at DATETIME NOT NULL, -- 创建时间updated_at DATETIME NOT NULL, -- 更新时间-- 主键索引CONSTRAINT pk_user_id PRIMARY KEY (user_id),-- 唯一索引CONSTRAINT uk_email UNIQUE (email),CONSTRAINT uk_phone_number UNIQUE (phone_number),-- 普通索引INDEX idx_username (username),INDEX idx_created_at (created_at)
);【强制】如果存储的字符串长度几乎相等，使用char定长字符串类型。
【强制】varchar是可变长字符串，不预先分配存储空间，长度不要超过5000，如果存储长度 大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。
【强制】表必备三字段:id,create_time,update_time,version,staus,
说明：其中id必为主键，类型为bigint unsigned、单表时自增、步长为1。create time,update time的类型均为datetime类型，前者现在时表示主动式创建，后者过去分词表示被动式更新。
【推荐】表的命名最好是遵循“业务名称_表的作用”。
正例:alipay_task / force_project / trade_config【推荐】库名与应用名称尽量一致。
【推荐】如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。
【推荐】字段允许适当冗余，以提高查询性能，但必须考虑数据一致。冗余字段应遵循:
- 不是频繁修改的字段。
- 不是唯一索引的字段。
- 不是 varchar 超长字段，更不能是 text 字段。
正例: 各业务线经常冗余存储商品名称，避免查询时需要调用 IC 服务获取。
【推荐】单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。
说明: 如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。
【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。
正例:无符号值可以避免误存负数，且扩大了表示范围。
2. 数据库索引规约
【强制】业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。
说明: 不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的; 另外， 即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律（“凡是可能出错的地方，迟早会出错”），必然有脏数据产生。
组合唯一索引： 假设有一个电商订单表 orders，需要保证同一用户（user_id）在同一时间（order_date）只能有一个特定商品（product_id）的订单。此时可以为这三个字段建立组合唯一索引。
CREATE TABLE orders (order_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- 主键user_id INT UNSIGNED NOT NULL, -- 用户IDproduct_id INT UNSIGNED NOT NULL, -- 商品IDorder_date DATE NOT NULL, -- 下单日期quantity INT UNSIGNED NOT NULL DEFAULT 1, -- 购买数量total_price DECIMAL(10, 2) NOT NULL, -- 总价-- 组合唯一索引：确保 user_id、product_id 和 order_date 的组合唯一CONSTRAINT uk_user_product_date UNIQUE (user_id, product_id, order_date)
);防止重复数据： 假设没有唯一索引，可能会由于程序逻辑错误，导致以下重复数据插入：
INSERT INTO orders (user_id, product_id, order_date, quantity, total_price)
VALUES (1, 101, '2024-12-21', 1, 100.00);INSERT INTO orders (user_id, product_id, order_date, quantity, total_price)
VALUES (1, 101, '2024-12-21', 1, 100.00); -- 重复数据通过唯一索引，数据库会直接报错，避免数据重复：
Error: Duplicate entry '1-101-2024-12-21' for key 'uk_user_product_date'提高查询效率：当查询某用户某天的特定商品订单时，唯一索引能直接命中：
SELECT * FROM orders WHERE user_id = 1 AND product_id = 101 AND order_date = '2024-12-21';由于是唯一索引，查询效率更高。
【强制】在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据 实际文本区分度决定索引长度。
说明: 索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为20的索引，区分度会高达 90% 。以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。
【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。 （查询一定要使用索引，不能走全表扫描）。
说明: 索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。
索引失效：使用 LIKE '%keyword' 或 LIKE '%keyword%' 时，查询会失去对索引的利用，触发全表扫描。例如：LIKE '%abc' 或 LIKE '%abc%'，因为无法预判字符串前缀，索引无法帮助快速定位匹配的数据。
查询性能低下：在数据量较大的情况下，全表扫描会占用大量资源，导致查询效率下降。
资源浪费：全模糊查询会浪费 CPU 和 IO 资源，影响其他查询操作，导致系统性能整体下降。
前缀匹配查询
- 使用
LIKE 'keyword%'或=来保证索引的利用。 - 这种方式可以让查询条件基于索引进行匹配，而不是全表扫描。
借助全文检索引擎：使用全文搜索引擎（如 Elasticsearch、Sphinx 或 MySQL FULLTEXT 索引），适合处理全文模糊匹配的场景。
自定义关键字拆分：如果必须支持模糊查询，可以通过应用层将字符串拆分为前缀、后缀等分段存储，使用组合索引来优化查询。
【推荐】如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能（注意最左匹配原则）。
正例: where a=? and b=? order by c; 索引:a_b_c
反例: 索引如果存在范围查询，那么索引有序性无法利用，如:WHERE a>10 ORDER BY b; 索引 a_b 无 法排序。 【推荐】利用覆盖索引来进行查询操作，避免回表。（就是的使用索引中就查询需要的值）
说明: 如果一本书需要知道第 11 章是什么标题，会翻开第 11 章对应的那一页吗?目录浏览一下就好，这个目录就是起到覆盖索引的作用。
正例: 能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查询的一种效 果，用 explain 的结果，extra 列会出现:using index。
- 减少 IO 操作：不需要回表，直接从索引中获取所需字段，减少磁盘 IO 和内存开销。
- 提高查询效率：索引通常存储在内存中，查询性能比直接访问表记录快。
- 优化性能：特别是在大表中，可以显著降低查询延迟。
CREATE TABLE users (user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- 主键username VARCHAR(50) NOT NULL, -- 用户名email VARCHAR(100) NOT NULL, -- 邮箱created_at DATETIME NOT NULL -- 创建时间
);错误示例：没有覆盖索引 如果查询以下语句：
SELECT user_id, username FROM users WHERE username = 'JohnDoe';如果只有以下索引：
CREATE INDEX idx_username ON users (username);查询时执行流程：
- 索引
idx_username用于定位到符合条件的记录。 - 然后需要回表（通过主键找到完整的记录），再获取
user_id和username的值。
正确示例：利用覆盖索引，为了避免回表，调整索引结构，使其包含查询所需的所有字段：
CREATE INDEX idx_username_user_id ON users (username, user_id);此时执行：
SELECT user_id, username FROM users WHERE username = 'JohnDoe';查询时直接利用覆盖索引 idx_username_user_id，不需要回表即可获得 user_id 和 username 的值。
实现多字段覆盖索引，如果需要查询多个字段，例如：
SELECT user_id, username, email FROM users WHERE username = 'JohnDoe';为了避免回表，可以创建以下覆盖索引：
CREATE INDEX idx_username_cover ON users (username, user_id, email);此时查询所需的 username、user_id 和 email 都被索引覆盖，查询直接从索引中获取数据，无需回表。
【推荐】利用延迟关联或者子查询优化超多分页场景（防止慢查询）。
说明: MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。
正例: 先快速定位需要获取的 id 段，然后再关联:
SELECT t1.* FROM 表 1 as t1, (select id from 表 1 where 条件 LIMIT 100000,20 ) as t2 where t1.id=t2.id。【推荐】SQL 性能优化的目标:至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。
说明:
- consts 单表中最多只有一个匹配行(主键或者唯一索引)，在优化阶段即可读取到数据。
- ref 指的是使用普通的索引(normal index)。
- range 对索引进行范围检索。
反例: explain 表的结果，type=index，索引物理文件全扫描，速度非常慢，这个 index 级别比较 range 还低，与全表扫描是小巫见大巫。
【推荐】建组合索引的时候，区分度最高的在最左边（最左索引原则）。
正例: 如果 where a=? and b=?，a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即可。
说明: 存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如:where c>? and d=? 那么即使 c 的区分度更高，也必须把 d 放在索引的最前列，即建立组合索引 idx_d_c。
【推荐】防止因字段类型不同造成的隐式转换，导致索引失效（防止索引失效）。
【参考】创建索引时避免有如下极端误解（表索引能够覆盖到底所有的查询要求即可，不宜多，不宜少）:
- 索引宁滥勿缺。认为一个查询就需要建一个索引。
- 吝啬索引的创建。认为索引会消耗空间、严重拖慢记录的更新以及行的新增速度。
- 抵制惟一索引。认为惟一索引一律需要在应用层通过“先查后插”方式解决。
3. 数据库SQL规范
【强制】不要使用count(列名)或count(常量)来替代count(*)，count(*)是SQL92定义的标 准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。
说明: count(*)会统计值为 NULL 的行，而count(列名)不会统计此列为 NULL值的行。
【强制】count(distinct col) 计算该列除 NULL 之外的不重复行数，注意 count(distinct col1, col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。
【强制】当某一列的值全是NULL时，count(col)的返回结果为0，但sum(col)的返回结果为 NULL，因此使用 sum()时需注意 NPE 问题。
正例: 可以使用如下方式来避免 sum 的 NPE 问题: SELECT IFNULL(SUM(column), 0) FROM table; 【强制】使用ISNULL()来判断是否为NULL值。
说明: NULL 与任何值的直接比较都为 NULL。
- NULL<>NULL 的返回结果是 NULL，而不是 false。
- NULL=NULL 的返回结果是 NULL，而不是 true。
- NULL<>1 的返回结果是 NULL，而不是 true。
反例: 在 SQL 语句中，如果在 null 前换行，影响可读性。select * from table where column1 is null and column3 is not null; 而`ISNULL(column)`是一个整体，简洁易懂。从性能数据上分析，`ISNULL(column)` 执行效率更快一些。
在 SQL 中，ISNULL() 是一个函数，常用于判断一个值是否为 NULL，并且可以替代 NULL 值为指定的值。不同的数据库管理系统（DBMS）在处理 NULL 值时可能会有所不同。ISNULL() 在某些 DBMS 中如 SQL Server 使用广泛，但在 MySQL 中常用 IFNULL() 或 COALESCE() 来替代。
MySQL 使用 IFNULL()： 在 MySQL 中，IFNULL() 用于判断一个表达式是否为 NULL，如果是，返回替代值。
IFNULL(expression, replacement_value)使用 IFNULL() 判断 NULL
如果 email 为 NULL，返回 'noemail@example.com'：
SELECT IFNULL(email, 'noemail@example.com') AS user_email FROM users;使用 IFNULL() 判断 NULL 与普通字段查询
如果 age 为 NULL，返回 'Unknown'：
SELECT name, IFNULL(CAST(age AS CHAR), 'Unknown') AS user_age FROM users;PostgreSQL 使用 COALESCE()： 在 PostgreSQL 和其他一些 SQL 数据库中，COALESCE() 函数用于返回第一个非 NULL 的值。它可以用于判断 NULL 值。
SQL Server 使用 ISNULL()：ISNULL() 函数在 SQL Server 中常用来判断字段值是否为 NULL，并且可以为 NULL 值指定一个替代值。
ISNULL(expression, replacement_value)使用 ISNULL() 判断 NULL
假设我们有一个 users 表，查询用户的 email，如果 email 为 NULL，则返回。
SELECT ISNULL(email, 'noemail@example.com') AS user_email FROM users;使用 ISNULL() 判断 NULL 与普通字段查询。
如果 age 字段为 NULL，返回 'Unknown'，否则返回实际年龄：
SELECT name, ISNULL(CAST(age AS VARCHAR), 'Unknown') AS user_age FROM users;使用 IS NULL 和 IS NOT NULL 处理 NULL 值时的注意点
- 在 SQL 中，
NULL不等于任何值，包括NULL本身。所以，不能使用=或!=来进行NULL值的比较。 - 必须使用
IS NULL或IS NOT NULL来明确判断字段是否为NULL。
SELECT product_id, product_name, price FROM products WHERE price IS NOT NULL;【强制】代码中写分页查询逻辑时，若count为0应直接返回，避免执行后面的分页语句。
import java.sql.*;public class PaginationExample {// 获取分页数据public static PaginationResult getPaginatedData(int page, int perPage) throws SQLException {Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/your_database", "username", "password");// 第一步：查询总记录数String countQuery = "SELECT COUNT(*) FROM users";Statement statement = connection.createStatement();ResultSet resultSet = statement.executeQuery(countQuery);// 获取总记录数resultSet.next();int totalCount = resultSet.getInt(1);// 如果总记录数为 0，直接返回空结果if (totalCount == 0) {return new PaginationResult(0, 0, 0, null);}// 第二步：执行分页查询String paginatedQuery = "SELECT * FROM users LIMIT ? OFFSET ?";PreparedStatement preparedStatement = connection.prepareStatement(paginatedQuery);preparedStatement.setInt(1, perPage);preparedStatement.setInt(2, (page - 1) * perPage);ResultSet paginatedResult = preparedStatement.executeQuery();// 获取分页数据ResultSetMetaData metaData = paginatedResult.getMetaData();int columnCount = metaData.getColumnCount();List<String> users = new ArrayList<>();while (paginatedResult.next()) {StringBuilder row = new StringBuilder();for (int i = 1; i <= columnCount; i++) {row.append(paginatedResult.getString(i)).append(" ");}users.add(row.toString());}// 返回分页结果return new PaginationResult(totalCount, page, perPage, users);}public static void main(String[] args) {try {PaginationResult result = getPaginatedData(1, 10);System.out.println(result);} catch (SQLException e) {e.printStackTrace();}}
}class PaginationResult {private int totalCount;private int page;private int perPage;private List<String> data;public PaginationResult(int totalCount, int page, int perPage, List<String> data) {this.totalCount = totalCount;this.page = page;this.perPage = perPage;this.data = data;}@Overridepublic String toString() {return "PaginationResult{" +"totalCount=" + totalCount +", page=" + page +", perPage=" + perPage +", data=" + data +'}';}
}无论使用 JDBC 还是 Spring Data JPA，关键的思路是通过先查询总记录数 (count) 来判断是否有数据，如果没有数据（count == 0），直接返回空结果，从而避免进行不必要的分页查询操作。
【强制】不得使用外键与级联，一切外键概念必须在应用层解决。
说明: (概念解释)学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学 生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机 低并发，不适合分布式、高并发集群;级联更新是强阻塞，存在数据库更新风暴的风险;外键影响数据库 的插入速度。
为什么禁止使用外键与级联？
性能问题
- 外键约束会影响插入和删除操作的速度：在数据库中，每次插入、删除或更新数据时，数据库必须检查外键约束是否满足。如果有多个表的外键关系，操作时需要多次检查约束，导致性能下降。
- 级联操作：如级联删除或更新，会导致多个表的数据修改。虽然这在某些场景下有用，但在高并发环境下，数据库需要保证事务的完整性和一致性，可能造成锁竞争和性能瓶颈。
可扩展性和移植性问题
- 跨数据库系统迁移困难：不同数据库系统的外键实现和支持程度不同。某些数据库可能不支持复杂的外键约束或有不同的语法和行为，迁移数据时需要额外的适配工作。
- 服务解耦：使用外键和级联操作使得数据库间的耦合变得更加紧密，这在微服务架构中尤为不可取。每个微服务应当拥有自己的数据存储，并在应用层处理数据一致性，而不是依赖数据库约束来实现。
数据库的复杂性
- 数据库设计复杂：外键和级联增加了数据库结构的复杂性，特别是当有多张表的外键依赖关系时，维护起来较为麻烦。数据库迁移、升级、数据恢复等操作可能会变得复杂。
假设有一个 orders 表，它引用了 users 表的用户 ID，而你在删除一个用户时需要删除与该用户相关的订单。因为没有外键约束，你必须在应用层执行以下步骤：
# 示例：使用 Python 和 SQLAlchemy 模拟外键删除约束from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from models import User, Order# 创建数据库连接和会话
engine = create_engine('mysql://username:password@localhost/dbname')
Session = sessionmaker(bind=engine)
session = Session()# 获取用户 ID
user_id = 123# 删除该用户相关的所有订单
orders_to_delete = session.query(Order).filter(Order.user_id == user_id).all()
for order in orders_to_delete:session.delete(order)# 删除用户
user_to_delete = session.query(User).filter(User.id == user_id).first()
session.delete(user_to_delete)# 提交事务
session.commit()操作流程：
- 查找相关订单：在删除用户之前，先查找与该用户相关的所有订单。
- 删除订单：删除相关的订单记录。
- 删除用户：最后删除用户。
通过应用层的逻辑来模拟外键约束，确保删除顺序正确，避免因外键关系导致的数据不一致。
【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。
【强制】数据订正(特别是删除或修改记录操作)时，要先select，避免出现误删除，确认无误才能执行更新语句。
- 避免误删除：如果没有先执行
SELECT确认数据，可能会因为条件设置错误（例如条件漏写、条件错误）导致删除了不该删除的记录，造成无法挽回的损失。 - 避免误修改：在修改数据之前，先查看哪些记录会被修改，确保目标记录正确。避免误修改不应修改的字段，导致数据错误。
- 提高操作的透明度：通过先查询，操作人员可以清楚知道将要删除或修改的数据，确保没有遗漏或错误。
- 备份保护：在进行删除或批量更新时，先
SELECT确认可以帮助快速备份需要更新的数据或记录（例如，通过导出）。
其他注意事项
- 使用事务（Transaction）：如果操作涉及多个步骤（如查询、删除、更新），使用事务确保操作的原子性。如果在某个步骤出现问题，可以回滚事务。
BEGIN;-- 确认查询
SELECT * FROM users WHERE age < 18;-- 删除操作
DELETE FROM users WHERE age < 18;COMMIT; -- 提交事务- 使用
LIMIT防止误操作大规模数据：在删除或更新时，使用LIMIT限制一次删除或更新的记录数，这样可以防止不小心操作过多记录。示例：
DELETE FROM users WHERE age < 18 LIMIT 100; -- 删除前100条记录- 备份数据：在执行批量删除或更新前，最好先备份受影响的数据，以便出现错误时可以恢复。例如，你可以导出受影响的记录：
SELECT * FROM users WHERE age < 18 INTO OUTFILE '/path/to/backup.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';- 审计日志：在执行修改或删除时，记录操作日志。确保所有删除和更新操作都有审计记录，便于后期追溯。
- 避免误删除或误修改：执行删除或修改操作时，一定要先进行
SELECT查询，确认符合条件的记录是正确的。 - 审慎操作：使用事务、备份数据以及添加确认机制（如脚本提示）来保护数据安全。
- 确保数据的一致性和完整性：即使在高效的自动化流程中，也应保证每一步的安全确认，减少人为错误的发生。
【强制】对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名(或 表名)进行限定。
说明: 对多表进行查询记录、更新记录、删除记录时，如果对操作列没有限定表的别名(或表名)，并且 操作列在多个表中存在时，就会抛异常。
正例: select t1.name from table_first as t1 , table_second as t2 where t1.id=t2.id; 反例: 在某业务中，由于多表关联查询语句没有加表的别名(或表名)的限制，正常运行两年后，最近在某个表中增加一个同名字段，在预发布环境做数据库变更后，线上查询语句出现出 1052 异常:Column 'name' in field list is ambiguous。 表示查询中存在 重复的列名，即在查询的结果集中，多个表或子查询中存在同名的列，MySQL 无法确定应该使用哪一个列。
这种情况通常发生在 联接查询 (JOIN) 中，当多个表中存在相同列名（如 name）时，MySQL 无法确定使用哪个表的 name 列。
联接查询中的列名冲突： 假设有两个表 users 和 orders，都包含 name 列，下面是一个错误的查询：
SELECT name FROM users JOIN orders ON users.id = orders.user_id;这个查询会抛出 1052 错误，因为 name 列在两个表中都有，而没有明确指定从哪个表获取。
使用表名或表别名
SELECT users.name FROM users JOIN orders ON users.id = orders.user_id;或者使用表别名来简化查询：
使用表别名
SELECT u.name FROM users u JOIN orders o ON u.id = o.user_id;在这个查询中，使用了表别名 u 和 o 来明确指定 name 列是从 users 表中获取的。
多个表中有相同列名
假设查询中涉及到多个表，并且这些表中有重复的列名。以下是一个可能的查询：
SELECT name, email
FROM users u
JOIN employees e ON u.id = e.user_id;如果 users 和 employees 表中都有 name 列，则会抛出 1052 错误。解决方法同样是明确指定列的来源：
SELECT u.name AS user_name, e.name AS employee_name, u.email
FROM users u
JOIN employees e ON u.id = e.user_id;在这个查询中，name 列被明确指定来自 users 表（u.name）和 employees 表（e.name），避免了列名冲突。
【推荐】SQL 语句中表的别名前加 as，并且以 t1、t2、t3、...的顺序依次命名。
说明: 别名可以是表的简称，或者是依照表在 SQL 语句中出现的顺序，以 t1、t2、t3 的方式命名。别名前加 as 使别名更容易识别。
正例: select t1.name from table_first as t1, table_second as t2 where t1.id=t2.id; 【推荐】in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控 制在 1000 个之内。
- SQL 查询优化和性能瓶颈：执行计划的复杂性：当
IN后面的集合非常大时，数据库的查询优化器可能会生成较为复杂的执行计划，这可能导致查询的执行效率变低，特别是当涉及到大量数据检索时。数据库需要逐个比对每个IN列表中的值，与表中的列进行匹配，如果集合元素过多，可能会影响查询的性能。 - 索引失效：无法利用索引优化：当使用
IN列表时，数据库通常会试图将IN列表中的多个值视为多个 OR 条件来处理。但如果IN后面的元素数量过多，可能会导致查询优化器无法有效使用索引，进而导致全表扫描，性能大幅下降。尤其是在一些复杂的查询中，或者当查询条件的列没有合适的索引时，IN的性能会受到影响。 - 数据库优化器处理：不同的数据库引擎（例如 MySQL、PostgreSQL、Oracle 等）在处理大量元素的
IN操作时，可能会遇到内部的性能限制。对于IN列表非常大的情况，数据库可能会尝试将其转化为多个小的查询来执行，这会增加额外的查询开销。 - 参数数量限制：许多数据库系统对 SQL 查询中的参数数量有限制。例如，MySQL 中单个
IN列表的最大元素数限制是 1000。当超过该限制时，会抛出错误，导致 SQL 无法执行。 - 内存消耗和优化问题：当
IN列表包含大量元素时，数据库需要将这些值加载到内存中进行比较，这将消耗更多的内存资源，尤其在高并发、大数据量的情况下，可能导致内存不足，进一步影响查询性能。
in 使用总结
IN的元素数量应控制在 1000 个以内，主要是为了避免性能瓶颈、内存消耗过大以及索引失效等问题。- 如果
IN列表中的元素过多，可以考虑分批查询、使用临时表、替代方案（如JOIN或EXISTS），这些方法能够提高查询效率，减少数据库的负担。 - 合理设计索引，并避免在高并发、数据量大的情况下频繁使用大型的
IN查询。
这些做法能够帮助避免在处理大规模查询时因 IN 操作导致的性能问题。
【强制】MySQL 中，推荐使用 utf8mb4 编码 而不是 utf8 编码。
在 MySQL 中，推荐使用 utf8mb4 编码 而不是 utf8 编码，特别是在需要处理多种语言和特殊字符（如表情符号、一些罕见的Unicode字符等）时。
utf8 和 utf8mb4 的区别
utf8是 MySQL 的早期实现，它只支持 最多 3 个字节的字符，即 U+0000 到 U+FFFF 范围的 Unicode 字符。它无法存储 4 字节的字符。utf8mb4是 MySQL 后来引入的完整 Unicode 编码，它支持 1 到 4 个字节 的字符，即可以存储所有 Unicode 字符，包括 4 字节字符（如表情符号、部分特殊字符等）。utf8mb4实际上是对utf8的扩展。
为什么选择 utf8mb4 而不是 utf8
utf8的局限性：
-
utf8并不是一个真正的 UTF-8 实现，它实际上只能处理 Unicode 的 1 到 3 字节 范围内的字符。这意味着它不能处理 4 字节字符，如 表情符号（emoji）、某些语言的特殊字符（例如某些少数民族字符），以及其他 4 字节的 Unicode 字符。- 在
utf8编码下，插入这些超出 3 字节的字符时，MySQL 会报错或丢失数据。
utf8mb4的优势：
-
utf8mb4能够支持 Unicode 的 1 到 4 字节 范围，完全兼容所有字符集，并能正确处理所有 Unicode 字符，包括表情符号（emoji）、历史符号、少数民族语言字符等。- 因为
utf8mb4能支持 4 字节字符，它是现代应用和多语言环境下的最佳选择。
使用 utf8mb4 编码的理由
- 兼容性：
-
utf8mb4是完整的 Unicode 实现，能够支持所有语言的字符和符号。如果你期望支持表情符号或其他 Unicode 扩展字符，必须使用utf8mb4。- 如果你的数据库中包含 emoji 或某些特殊符号，而你使用了
utf8编码，MySQL 会抛出错误或者数据会被截断。
- 无字符损失：
-
- 使用
utf8mb4能确保字符的存储不被丢失或错误地转换，特别是在国际化应用中，确保所有字符都可以正确保存。
- 使用
utf8mb4是向下兼容的：
-
utf8mb4完全兼容utf8，也就是说，如果数据库或表使用了utf8mb4，它可以存储utf8能存储的所有字符，但反过来就不行。换句话说，utf8mb4是utf8的超集。
如何在 MySQL 中使用 utf8mb4
创建数据库时使用 utf8mb4 编码：
CREATE DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;创建表时使用 utf8mb4 编码：
CREATE TABLE my_table (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(255) NOT NULL,description TEXT
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;修改已有表的字符集为 utf8mb4：
如果你的表已经使用 utf8 编码，可以通过以下命令将字符集转换为 utf8mb4：
ALTER TABLE my_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;修改已有列的字符集为 utf8mb4：
ALTER TABLE my_table MODIFY COLUMN name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;13.【参考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但TRUNCATE无事务且不触发 trigger，有可能造成事故，故不建议在开发代码中使用此语句。
说明: TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。
4. ORM规约
【强制】在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。
说明:
- 增加查询分析器解析成本
- 增减字段容易与 resultMap 配置不一致
- 无用字段增加网络 消耗，尤其是 text 类型的字段。
假设我们需要查询 users 和 orders 两个表的数据，可以使用 JOIN 操作，并且避免使用 * 来避免字段冲突。
<!-- 正确示例：明确指定需要查询的字段，避免字段冲突 --><select id="selectUserOrders" resultMap="userOrderResultMap">SELECT u.id AS user_id, u.username, u.email, o.id AS order_id, o.total_price FROM users uJOIN orders o ON u.id = o.user_id;
</select>在这个例子中，我们通过 AS 为表 users 和 orders 中同名的 id 字段添加别名（user_id 和 order_id），避免了字段冲突。这样做可以确保查询结果的字段名称清晰且不重复。
定义 resultMap 映射：
为了将查询结果正确映射到 Java 对象，我们需要定义 resultMap 来映射字段到对象属性。例如，我们有 User 和 Order 类，分别表示 users 和 orders 表。
<resultMap id="userOrderResultMap" type="UserOrderDTO"><result property="userId" column="user_id"/><result property="username" column="username"/><result property="email" column="email"/><result property="orderId" column="order_id"/><result property="totalPrice" column="total_price"/>
</resultMap>这里，UserOrderDTO 是我们查询结果的封装对象。字段 user_id 映射到 userId，username 映射到 username 等。通过这种方式，我们可以确保查询结果按照我们定义的字段正确映射到 Java 对象。
public class UserOrderDTO {private int userId;private String username;private String email;private int orderId;private BigDecimal totalPrice;// Getters and Setters
}使用 sql 标签来引用公共 SQL 片段
首先，我们可以在 MyBatis 的 XML 配置文件中定义一个公共的 SQL 片段，比如查询 users 表的基本字段。使用 sql 标签，可以在多个查询中复用这个片段。
<!-- 定义公共的 SQL 片段 -->
<sql id="userColumns">id, username, email
</sql>接下来，我们可以在查询中引用这个 SQL 片段，避免重复书写这些字段。
<!-- 查询所有用户的基本信息 -->
<select id="selectUsers" resultType="User">SELECT <include refid="userColumns"/>FROM users;
</select>复用 SQL 片段（多次引用）：如果需要查询其他表的数据，或者想要使用相同的列来进行其他操作，我们仍然可以引用同一个 SQL 片段。
<!-- 查询所有订单的用户信息 -->
<select id="selectUserOrders" resultType="UserOrderDTO">SELECT <include refid="userColumns"/>, o.id AS order_id, o.total_price FROM users uJOIN orders o ON u.id = o.user_id;
</select>使用 sql 标签来处理复杂的查询条件： 如果查询条件复杂，常常需要复用某些 SQL 片段，可以使用 sql 标签来定义条件部分，方便引用。
使用 sql 标签来处理复杂的查询条件： 如果查询条件复杂，常常需要复用某些 SQL 片段，可以使用 sql 标签来定义条件部分，方便引用。
<!-- 定义一个查询条件 -->
<sql id="userWhereCondition">WHERE status = #{status} AND created_at > #{createdAt}
</sql><!-- 查询特定状态的用户 -->
<select id="selectUsersByStatus" resultType="User">SELECT <include refid="userColumns"/>FROM users<include refid="userWhereCondition"/>
</select>动态 SQL 片段： 对于动态查询条件，可以结合 MyBatis 的动态 SQL 功能，如 if、where、trim 等标签来生成灵活的查询条件。
<!-- 动态查询条件 -->
<select id="selectUsersByDynamicConditions" resultType="User">SELECT <include refid="userColumns"/>FROM users<where><if test="username != null">AND username = #{username}</if><if test="email != null">AND email = #{email}</if></where>
</select>在这个例子中，我们使用了 <where> 标签来动态生成 SQL 条件，只有在参数不为 null 时才会添加对应的条件。
【强制】POJO类的布尔属性不能加is，而数据库字段必须加is_，要求在resultMap中进行 字段与属性之间的映射。
说明: 参见定义 POJO 类以及数据库字段定义规定，在 sql.xml 增加映射，是必须的。
假设你有一个 users 表，表中有一个布尔字段 is_active，表示用户是否激活。POJO 类中定义的布尔属性是 active，没有 is 前缀。我们需要在 resultMap 中明确将 is_active 字段映射到 active 属性。
数据库表结构：
users 表：
| Column | Type |
| id | INT |
| username | VARCHAR |
| | VARCHAR |
| is_active | TINYINT(1) |
POJO 类（User 类）
在 POJO 类中，布尔类型的属性不加 is 前缀，直接使用 active 属性名。
public class User {private int id;private String username;private String email;private boolean active; // 布尔属性名不加 "is"// Getters and Setterspublic int getId() {return id;}public void setId(int id) {this.id = id;}public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}public String getEmail() {return email;}public void setEmail(String email) {this.email = email;}public boolean isActive() {return active;}public void setActive(boolean active) {this.active = active;}
}resultMap 映射配置
在 MyBatis 的 XML 映射文件中，我们需要显式地使用 resultMap 来将数据库的 is_active 字段映射到 User 类的 active 属性。
<resultMap id="userResultMap" type="User"><id property="id" column="id"/><result property="username" column="username"/><result property="email" column="email"/><!-- 映射数据库字段 is_active 到 POJO 类的 active 属性 --><result property="active" column="is_active"/>
</resultMap><select id="selectUserById" resultMap="userResultMap">SELECT id, username, email, is_activeFROM usersWHERE id = #{id};
</select>【强制】不要用resultClass当返回参数，即使所有类属性名与数据库字段一一对应，也需要定义<resultMap>; 反过来，每一个表也必然有一个<resultMap>与之对应。
说明:配置映射关系，使字段与DO类解耦，方便维护。
为什么推荐使用 resultMap？
- 明确的映射关系：使用
resultMap明确指出数据库列和 Java 对象属性之间的关系，避免了 MyBatis 自动映射时可能出现的歧义，尤其是在属性名与列名不一致的情况下。 - 字段别名和处理：
resultMap可以定义字段的别名，将数据库字段映射到 Java 类中的属性名，即使字段名不一致，也能做到灵活处理。 - 增强的映射控制：
resultMap提供了更多的配置选项，如复杂映射（例如嵌套对象映射），以及对字段的额外处理（如日期格式化等）。 - 提升代码可读性与可维护性：通过显式的映射，可以提高代码的可读性，其他开发者在阅读代码时，能明确知道字段和属性之间的关系。
假设我们有一个 users 表和一个 User 类，users 表包含 id、username、email 和 password 等字段，User 类有对应的属性，但我们不直接使用 resultClass，而是通过 resultMap 显式定义映射关系。
表结构（users）:
| Column | Type |
| id | INT |
| username | VARCHAR |
| | VARCHAR |
| password | VARCHAR |
public class User {private int id;private String username;private String email;private String password;// Getters and Setters
}使用 resultMap 进行映射： 即使 User 类的属性和 users 表的字段一一对应，依然推荐使用 resultMap 进行明确映射。
<resultMap id="userResultMap" type="User"><id property="id" column="id"/><result property="username" column="username"/><result property="email" column="email"/><result property="password" column="password"/>
</resultMap><select id="selectUserById" resultMap="userResultMap">SELECT id, username, email, passwordFROM usersWHERE id = #{id};
</select>复杂类型映射（嵌套映射）
如果表中有外键关系，或者你需要返回嵌套对象（比如 User 类中的 Address 类属性），则可以使用 resultMap 定义嵌套映射。例如，假设 users 表有一个外键 address_id，引用 addresses 表。
public class Address {private int id;private String street;private String city;// Getters and Setters
}users 表结构更新：
| Column | Type |
| id | INT |
| username | VARCHAR |
| | VARCHAR |
| password | VARCHAR |
| address_id | INT |
Address 表结构：
| Column | Type |
| id | INT |
| street | VARCHAR |
| city | VARCHAR |
定义 resultMap 和嵌套映射：
<resultMap id="addressResultMap" type="Address"><id property="id" column="id"/><result property="street" column="street"/><result property="city" column="city"/>
</resultMap><resultMap id="userResultMap" type="User"><id property="id" column="id"/><result property="username" column="username"/><result property="email" column="email"/><result property="password" column="password"/><!-- 嵌套映射 --><association property="address" javaType="Address" resultMap="addressResultMap"/>
</resultMap><select id="selectUserWithAddressById" resultMap="userResultMap">SELECT u.id, u.username, u.email, u.password, a.id AS address_id, a.street, a.cityFROM users uLEFT JOIN addresses a ON u.address_id = a.idWHERE u.id = #{id};
</select>在这个例子中：
- 嵌套映射: 我们通过
<association>标签将users表中的address_id字段映射到User类中的Address对象。这意味着，当查询users表时，MyBatis 会使用addressResultMap将address_id映射到Address类的相关属性。 <association>标签: 用于处理嵌套的映射关系，指定嵌套对象的resultMap和映射的属性。
使用 resultMap 进行多表查询
假设我们还需要查询多个表（比如 orders 表），也可以使用类似的方式来映射结果。
查询用户和订单信息：
<resultMap id="orderResultMap" type="Order"><id property="id" column="id"/><result property="totalPrice" column="total_price"/><result property="userId" column="user_id"/>
</resultMap><select id="selectUserWithOrdersById" resultMap="userResultMap">SELECT u.id AS user_id, u.username, u.email, u.password,o.id AS order_id, o.total_priceFROM users uLEFT JOIN orders o ON u.id = o.user_idWHERE u.id = #{id};
</select>在这个查询中，我们使用 LEFT JOIN 将 users 表和 orders 表连接，并将 orders 表的字段映射到 Order 类。通过 resultMap，我们将查询结果映射到 User 和 Order 对象。
总结
- 即使字段和属性一一对应，也应该使用
resultMap。这种做法明确了映射关系，提升了代码的可读性和可维护性。 resultMap提供了更多的灵活性，可以应对字段名不一致、嵌套对象、复杂查询等复杂情况。- 嵌套映射：使用
<association>标签可以实现多表查询时的对象嵌套映射。 - 避免使用
resultClass：直接使用resultClass可能会导致自动映射的问题，resultMap让你更加精确地控制字段到属性的映射。
insert 标签插入组合对象
在 MyBatis 中，如果你需要插入一个组合对象（如包含多个属性或嵌套对象的对象），你可以使用 insert 标签来实现。插入操作通常会使用 parameterType 来指定接收传入参数的 Java 类，然后在 SQL 语句中通过占位符来插入对象的属性值。
数据库表结构
假设我们有两个表：users 和 addresses。
users 表：
| Column | Type |
| id | INT |
| username | VARCHAR |
| | VARCHAR |
| address_id | INT |
addresses 表：
| Column | Type |
| id | INT |
| street | VARCHAR |
| city | VARCHAR |
Java 类
我们定义了 User 类，它包含 Address 类的引用，表示一个组合对象。
public class User {private int id;private String username;private String email;private Address address; // Address 类是嵌套的对象// Getters and Setters
}public class Address {private int id;private String street;private String city;// Getters and Setters
}insert 标签的配置
假设你要插入一个 User 对象，并且 User 对象中包含一个 Address 对象。你需要在 insert 标签中编写插入语句，同时使用 parameterType 来指明参数类型是 User。
<insert id="insertUser" parameterType="User"><!-- 插入到 users 表 -->INSERT INTO users (username, email, address_id)VALUES (#{username}, #{email}, (SELECT id FROM addresses WHERE street = #{address.street} AND city = #{address.city}));
</insert>多表插入的情况
有时你可能需要先插入一个表（例如 addresses 表），然后将插入后的 id 作为外键插入到其他表（例如 users 表）。这是一个典型的多表插入场景。
假设我们需要先插入 address，然后将其 id 插入到 users 表中：
<insert id="insertAddressAndUser" parameterType="User"><!-- 插入地址 -->INSERT INTO addresses (street, city) VALUES (#{address.street}, #{address.city});<!-- 获取插入后的 address_id --><selectKey keyProperty="address.id" resultType="int" order="AFTER">SELECT LAST_INSERT_ID();</selectKey><!-- 插入用户 -->INSERT INTO users (username, email, address_id) VALUES (#{username}, #{email}, #{address.id});
</insert>解析：
<selectKey>标签: 用于获取刚插入的address的id，并将其设置到User对象的address.id属性中。LAST_INSERT_ID(): 用来获取上一次插入操作的自动生成的 ID。这里，我们通过SELECT LAST_INSERT_ID()来获取address的id。- 多表插入: 第一步插入
address，第二步通过获取插入后的address.id来将其插入到users表的address_id字段。
【强制】sql.xml 配置参数使用:#{}，#param# 不要使用${} 此种方式容易出现 SQL 注入。
使用 #{} 传递参数 (推荐做法)
#{} 适用于所有的参数绑定，它能够安全地处理各种数据类型。
<select id="selectUserById" resultType="User">SELECT id, username, emailFROM usersWHERE id = #{id}
</select>解释：使用 #{id}，MyBatis 会将 id 作为一个预处理语句的参数，安全地传递给 SQL 引擎，防止 SQL 注入。
// 假设我们有一个 SqlSession 实例
SqlSession sqlSession = sqlSessionFactory.openSession();// 查询用户
User user = sqlSession.selectOne("selectUserById", 1);使用 ${} 存在 SQL 注入风险（避免使用）
#{} 是安全的，而 ${} 直接将参数嵌入到 SQL 语句中，容易导致 SQL 注入攻击。使用 ${} 只有在动态 SQL 中必须拼接表名、列名、排序字段等的时候才可以使用，但这类情况也需要小心处理。
<!-- 错误示范：直接拼接 SQL 语句 -->
<select id="selectUserByColumn" resultType="User">SELECT id, username, emailFROM usersWHERE ${columnName} = #{value}
</select>在这个例子中，${columnName} 会将传入的 columnName 直接替换到 SQL 语句中，如果 columnName 的值来自用户输入，攻击者就可以利用这个特性进行 SQL 注入。
【强制】iBATIS 自带的 queryForList(String statementName,int start,int size)不推荐使用。
说明: 其实现方式是在数据库取到 statementName 对应的 SQL 语句的所有记录，再通过 subList 取 start,size 的子集合。
正例: Map<String, Object> map = new HashMap<>(16); map.put("start", start); map.put("size", size);
【强制】不允许直接拿HashMap与Hashtable作为查询结果集的输出。
反例: 某同学为避免写一个<resultMap>xxx</resultMap>，直接使用 HashTable 来接收数据库返回结 果，结果出现日常是把 bigint 转成 Long 值，而线上由于数据库版本不一样，解析成 BigInteger，导致线 上问题。
问题原因：
- 类型不明确：
HashMap和Hashtable的键和值是Object类型，无法在编译时进行类型检查，容易发生类型转换错误。 - 代码可读性差：直接使用
HashMap或Hashtable返回结果时，不容易理解字段含义，也不方便重构和维护。 - 容易出错：如果查询结果的字段有变动，无法通过类型安全检查及时发现。
因此，推荐使用强类型的 POJO 类来映射查询结果，而不是直接使用 HashMap 或 Hashtable。
不推荐的方式（使用 HashMap）
<select id="selectUserById" resultType="java.util.HashMap">SELECT id, username, emailFROM usersWHERE id = #{id}
</select>问题原因：
- 查询结果直接映射到
HashMap，其中键是字段名（例如：id、username、email），值是字段对应的值。HashMap本身没有类型信息，这样做容易让程序员在处理数据时出错。 - 如果数据库字段发生变化，或者字段的名字与 POJO 类属性不同，可能会导致错误。
推荐的方式（使用 POJO 类）
正确的做法是使用 POJO 类（即普通 Java 对象）来映射查询结果，这样可以确保类型安全、提高代码可读性，并且在字段变化时也能更容易地进行维护。
创建 POJO 类
假设我们查询的是用户信息，可以创建一个 User 类。
@Data
public class User {private int id;private String username;private String email;
}使用 resultType 或 resultMap 映射 POJO 类
在 MyBatis 的 XML 配置文件中，我们可以使用 resultType 或 resultMap 将查询结果映射到 User 类。
方法一：使用 resultType
<select id="selectUserById" resultType="User">SELECT id, username, emailFROM usersWHERE id = #{id}
</select>方法二：使用 resultMap（更灵活的方式）
使用 resultMap 更加灵活，尤其是当数据库字段与 POJO 属性不完全一致时，或者当我们需要处理复杂的映射关系时。
<resultMap id="userResultMap" type="User"><id property="id" column="id"/><result property="username" column="username"/><result property="email" column="email"/>
</resultMap><select id="selectUserById" resultMap="userResultMap">SELECT id, username, emailFROM usersWHERE id = #{id}
</select>查询并使用结果
在 Java 代码中，通过 SqlSession 执行查询操作，MyBatis 会将结果映射到 User 对象中。
SqlSession sqlSession = sqlSessionFactory.openSession();
User user = sqlSession.selectOne("selectUserById", 1);// 输出查询结果
System.out.println("User ID: " + user.getId());
System.out.println("Username: " + user.getUsername());
System.out.println("Email: " + user.getEmail());总结与最佳实践：
- 避免使用
HashMap或Hashtable作为查询结果：直接将查询结果映射到HashMap或Hashtable会丧失类型安全性，导致代码可维护性差，容易出错。 - 使用 POJO 类来接收查询结果：通过强类型的 POJO 类进行映射，可以确保类型安全和更好的代码可读性。
- 使用
resultMap或resultType映射查询结果：当数据库字段与 POJO 类属性一致时，可以直接使用resultType；如果需要更复杂的字段映射，则使用resultMap来显式指定字段与属性的映射关系。 - 类型安全的好处：
-
- 编译时检查：使用 POJO 类可以让编译器检查类型，避免运行时发生类型转换错误。
- 代码可读性和可维护性：POJO 类使得字段更具语义，代码更容易理解和维护。
- 数据库字段变化的可控性：当数据库字段发生变化时，修改 POJO 类中的属性名或者添加新字段可以更容易地保持同步。
【强制】更新数据表记录时，必须同时更新记录对应的update_time字段值为当前时间。
在数据库表中更新记录时，通常会要求同时更新 update_time 字段，以记录数据的最后更新时间。这是一个常见的做法，可以通过触发器、应用逻辑或者在 SQL 语句中显式设置来实现。
使用 SQL 语句进行更新
假设我们有一个 users 表，表结构如下：
| Column | Type |
| id | INT |
| username | VARCHAR |
| | VARCHAR |
| update_time | DATETIME |
每次更新用户记录时，我们希望同时更新 update_time 字段为当前时间。
更新 SQL 语句
在 MyBatis 中，我们可以通过 UPDATE 语句来更新记录，并且使用 NOW()（MySQL 的当前时间函数）来更新 update_time 字段。
<update id="updateUser" parameterType="User">UPDATE usersSET username = #{username},email = #{email},update_time = NOW()WHERE id = #{id}
</update>解释：
username和email是要更新的字段。update_time = NOW()会将update_time字段的值设置为当前时间。WHERE id = #{id}用于指定更新的记录。
使用触发器（Trigger）
如果你希望在数据库层面自动更新 update_time 字段，可以使用数据库触发器来实现。在每次更新某个记录时，触发器会自动更新 update_time 字段。
DELIMITER $$CREATE TRIGGER before_user_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGINSET NEW.update_time = NOW();
END$$DELIMITER ;解释：
- 这个触发器会在
users表的每次UPDATE操作之前触发。 NEW.update_time = NOW()将把update_time字段设置为当前时间。- 这样，每次更新
users表中的数据时，update_time字段都会自动更新。
推荐做法：应用层和数据库层的配合，即通过 SQL 显式更新 update_time 字段，同时也可以使用触发器作为补充。这样能确保数据一致性和准确性。
【推荐】不要写一个大而全的数据更新接口。传入为POJO类，不管是不是自己的目标更新字 段，都进行updatetablesetc1=value1,c2=value2,c3=value3; 这是不对的。执行SQL时， 不要更新无改动的字段，一是易出错;二是效率低;三是增加 binlog 存储。
你提到的“不要写一个大而全的数据更新接口”实际上是在强调 只更新实际发生改变的字段，这有助于避免更新无关字段，提高数据库操作的效率，减少出错的风险，并且避免增加不必要的日志存储（如 binlog）或数据传输负担。下面是对这个问题的详细解释和示例。
问题分析：
- 易出错：如果你更新了所有字段（即使字段没有变化），可能会在无意中覆盖掉正确的值，或者导致字段值的错误。
- 效率低：如果不必要的字段被更新，数据库需要进行不必要的操作，这会浪费资源。
- 增加 binlog 存储：在 MySQL 等数据库中，所有更新操作都会被记录到 binlog 中。无必要的字段更新会导致更多的 binlog 记录，增加存储和网络负担。
推荐做法：
- 只更新变动的字段：动态检查每个字段是否发生变化，如果发生了变化才执行更新操作。
- 使用
UPDATE语句时避免不必要的字段更新。 - 提供灵活的更新接口：根据传入的参数只更新实际发生变化的字段。
手动检查字段变化（适用于简单的更新逻辑）
当 POJO 类的字段发生变化时，动态构建 UPDATE 语句，只有发生变化的字段才会被更新。
POJO 类示例：假设我们有一个 User 类，包含以下属性：
@Data
public class User {private Integer id;private String username;private String email;private Integer age;private Date updateTime;// Getters and Setters
}更新接口：可以使用 MyBatis 的动态 SQL 标签 <set> 来仅更新实际发生变化的字段。
<update id="updateUser" parameterType="User">UPDATE users<set><if test="username != null">username = #{username},</if><if test="email != null">email = #{email},</if><if test="age != null">age = #{age},</if><if test="updateTime != null">update_time = #{updateTime},</if></set>WHERE id = #{id}
</update>解释：
<set>标签：MyBatis 中的<set>标签会自动去掉末尾多余的逗号。<if>标签：用于判断字段是否为空或者是否发生变化，如果发生变化，则更新该字段。- 如果字段值发生变化，SQL 语句会更新该字段；如果没有变化（字段值为
null），则不会更新。
【参考】@Transactional事务不要滥用。事务会影响数据库的QPS，另外使用事务的地方需 要考虑各方面的回滚方案，包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等。
【参考】<isEqual>中的 compareValue 是与属性值对比的常量，一般是数字，表示相等时 带上此条件;<isNotEmpty>表示不为空且不为 null 时执行;<isNotNull>表示不为 null 值 时执行。
在 MyBatis 中，<isEqual>, <isNotEmpty>, 和 <isNotNull> 是常用的动态 SQL 标签，用于根据不同条件生成 SQL 语句的不同部分。它们通常用于 WHERE 子句中，来控制 SQL 语句的生成。以下是每个标签的解释及其使用示例：
<isEqual>：用于判断字段是否等于某个常量（如 1），条件成立时才会生成对应的 SQL。
<isNotEmpty>：检查属性是否不为空且不为 null。如果属性的值不为空或 null，则会加入 SQL 条件。
<isNotNull>：检查属性是否不为 null。如果属性不为 null，则会加入 SQL 条件。<select id="selectUserByCondition" resultType="User">SELECT id, username, email, is_activeFROM usersWHERE 1 = 1<isEqual property="isActive" compareValue="1">AND is_active = 1</isEqual><isNotEmpty property="username">AND username = #{username}</isNotEmpty><isNotNull property="email">AND email IS NOT NULL</isNotNull>
</select>博文参考
《阿里巴巴java开发规范》