theme: fancy

数据库

名词解释：

• 数据库（Database，DB）是一个有组织的、统一管理的数据集合，一个存数据的地方。
• 数据库管理系统（Database Management System，DBMS）：用来操纵和管理数据库的计算机软件，例如 MySQL、Oracle、SqlServer 等，都是数据库管理系统。
• 表（table）：某种特定类型数据的结构化清单。在表中的数据是同一种类型的数据或清单，即某一类数据的集合。

一个数据库系统中可以包含很多数据库，每个数据库中又可以包含多个表，表的一列称为一个字段（field/column），一行是一条记录（record/ row ） 。

表中每一行都应该有一列可以唯一标识自己，并将该列设置为主键（Primary Key） 。原则上，每个表都有主键。主键列中的值不允许修改或更新，which means “实体完整性”。

SQL

SQL（Structured Query Language，结构化查询语言），用来从数据库中查数取数以及创建新的数据库。

SQL 用关键字、表名、列名等组合成 SQL 语句来描述操作的内容。

不区分大小写，空格会被忽略

以下代码基于 MySQL 语法，其他数据库可能有所不同。

Table 增删改查

CREATE DATABASE test; -- 创建数据库
DROP DATABASE test; -- 删除数据库

-- 创建表
CREATE TABLE user_info
(
  id CHAR(8) NOT NULL, -- NOT NULL 是这一列的约束
  name VARCHAR(20),
  email VARCHAR(50),
  age INTEGER,
  PRIMARY KEY (id) -- 设置主键
);

DROP TABLE user_info; -- 删除表

ALTER TABLE user_info ADD COLUMN update_date VARCHAR(100); -- 添加列
ALTER TABLE user_info DROP COLUMN update_date; -- 删除列

ALTER TABLE user_info RENAME TO vip_user_info

-- 还有一个好用的东西：视图，可以把查询结果包装成一个新的虚拟表
-- 在大数据量时能简化复杂的 SQL 语句, 后续可以基于 test_view 做其他查询
CREATE VIEW test_view AS SELECT * FROM user_info WHERE is_vip = 1;
-- 删除视图
DROP VIEW test_view;

在表的设计过程中应该充分考虑未来可能的需求，避免后面对表的结构做大改动。

SELECT 查询

SELECT * FROM <table_name>; -- 查询 table 中所有数据

SELECT * FROM <table_name> WHERE <some_condition>; -- 条件查询

SELECT id, name FROM user_info; -- 让结果只包含某些列

SELECT DISTINCT group_id FROM tasks; -- 去重
-- DISTINCT 关键字作用于所有的列，不仅仅是跟在其后的那一列，会对多列组合的结果去重

SELECT  group_id FROM tasks LIMIT 5; -- 限制行数，LIMIT 是 MySQL 的语法，其他数据库中有所不同
SELECT group_id FROM tasks LIMIT 5 OFFSET 5; -- OFFSET 指定从哪开始（第一行是 0）

SELECT id, name user_name FROM user_info; -- 重命名结果集中的列
SELECT id, name FROM user_info WHERE age = 28;

SELECT id, name，age FROM user_info ORDER BY age; -- 让结果按 age 升序排序，一般是以主键排序
SELECT id, name，age FROM user_info ORDER BY age DESC; -- 倒序
-- ORDER BY 必须位于末尾
-- 如果想在多个列上进行降序排序，必须对每一列指定 DESC 关键字

SELECT id, name, age
FROM user_info
WHERE is_vip = 1
ORDER BY age DESC; -- ORDER BY 在 WHERE 之后

SELECT name task_name FROM tasks WHERE creator = '[email protected]';

SELECT id, name, age
FROM user_info
WHERE is_vip = 1
ORDER BY age DESC
LIMIT 10 OFFSET 0; -- 分页查询

SELECT COUNT(*) FROM user_info; -- 统计有多少条记录
-- 除 COUNT 之外还有 SUM、AVG、MAX、MIN 等聚合函数

SELECT
  s.id sid,
  s.name,
  s.gender,
  s.score,
  c.id cid,
  c.name cname
FROM students s, classes c; -- 多表查询

通配符

通配符可以创建比较特定数据的搜索模式，在搜索子句中使用通配符，必须使用 LIKE 操作符。

通配符搜索只能用于文本字段（字符串） ，非文本数据类型字段不能使用通配符搜索。

% 通配符表示任何字符出现任意次数：

-- 找出所有任务名以 GSB 开头的任务
-- 在 MySQL 中试了下不区分大小写
SELECT name task_name FROM tasks WHERE name LIKE 'GSB%';

-- 找出所有任务名以 GSB 开头、以 test 结尾的任务
SELECT name task_name FROM tasks WHERE name LIKE 'GSB%test';

-- 反过来，不以 GSB 开头
SELECT name task_name, creator FROM tasks WHERE NOT name LIKE 'GSB%';

_ 通配符只匹配单个字符：

-- 找出所有任务名以 GSB 开头，且后面只有两个字符的任务
SELECT name task_name FROM tasks WHERE name LIKE 'GSB__';

-- 输出
GSB01
GSB测试

[]通配符 用来指定一个字符集，必须匹配指定位置的一个字符：

-- 找出所有任务名以 G、T、C 开头的任务
-- MySQL 不支持集合
SELECT name task_name FROM tasks WHERE name LIKE '[G T C]%';

通配符要谨慎使用：

1. 能不用，就别用，优先考虑使用其他操作符；
2. 如果非要用，尽量把通配符放在查询语句末尾，放在前面搜索起来很慢。

计算字段

简单的查询有时无法满足复杂的应用场景，我们往往需要做一些字段的计算。

比如我们想把两个字段的信息整合起来组成一个值：

-- 以 MySQL 为例，其他数据库的操符略有不同
SELECT Concat(name, '(', creator, ')') FROM tasks;
-- 重命名为 task_info
SELECT Concat(name, '(', creator, ')') AS task_info FROM tasks;

算数计算：

-- 支持 +,-,*,/
SELECT prod_id,
  quantity,
  item_price,
  quantity＊item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;

大多数 SQL 支持一些处理函数：处理字符串（删除、转换等）、数值计算（加减乘除、绝对值等）、日期和时间处理等。需要用再去查阅文档吧 🧚🏿♀️。

还有一些聚集函数：

AVG() -- 某列的平均值
COUNT() -- 某列的行数
MAX() -- 某列的最大值
MIN() -- 某列的最小值
SUM() -- 某列值的和

SELECT AVG(price) AS avg_price FROM Products;
SELECT AVG(DISTINCT price) AS avg_price FROM Products;

-- 支持多个
SELECT COUNT(＊) AS num_items,
  MIN(prod_price) AS price_min,
  MAX(prod_price) AS price_max,
  AVG(prod_price) AS price_avg
FROM Products;

分组计算

❑ GROUP BY子句可以包含任意数目的列，因而可以对分组进行嵌套；

❑ 如果在 GROUP BY 子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）；

❑ GROUP BY 子句必须出现在 WHERE 子句之后，ORDER BY 子句之前。

SELECT
  creator, COUNT(*)
AS num_tasks 
FROM tasks 
GROUP BY creator 
ORDER BY num_tasks DESC;

WHERE 过滤行，HAVING 过滤分组：

SELECT creator, COUNT(*) 
AS num_tasks 
FROM tasks 
GROUP BY creator 
HAVING num_tasks >= 20
ORDER BY num_tasks;

分组和排序：

SELECT 子句顺序：

SELECT -- FROM（检索表）-- WHERE（行级过滤）-- GROUP BY（分组说明）-- HAVING（组级过滤）-- ORDER BY（输出排序）

子查询

把多个查询组合成一条语句，由内向外执行，常用于 WHERE 的 IN 操作符中：

-- 查找所有购买了 'RGAN01' 的客户 id
SELECT cust_id
FROM Orders
WHERE order_num IN (
  SELECT order_num
  FROM OrderItems
  WHERE prod_id = 'RGAN01'
);

-- 查找每个客户的订单数量
SELECT cust_name,
  cust_state,
  (SELECT COUNT(＊)
  FROM Orders
  WHERE Orders.cust_id = Customers.cust_id)
AS orders
FROM Customers
ORDER BY cust_name;

组合查询

执行多条 SELECT 语句，将它们的结果组合成一个结果集。操作符 UNION，UNION 中的每个查询必须包含相同的列。UNION 会从查询结果集中自动去除重复的行，如果你想保留重复的行，可以使用 UNION ALL。

SELECT
  name, creator 
FROM tasks 
WHERE name LIKE 'GSB%' UNION
SELECT 
  name,
  creator 
FROM tasks 
WHERE creator = '[email protected]'
-- cost 0.01s

SELECT
  name, creator 
FROM
  tasks 
WHERE
  name LIKE 'GSB%' AND creator = '[email protected]'
-- cost 0.008s

连接查询

关系型数据库中，关系表的设计原则是数据独立，把信息分解成多个表，一类数据一个表，各个表通过某些共同的值相互关联（所以才叫关系型数据库）。

数据存在多个表中，我们怎样用一条 SELECT 语句检索出数据呢？答案是使用连接查询。SQL 最强大的功能之一就是能在数据查询的执行中连接（join）表。

-- 假设我们有一个评测任务表 evaluate_tasks，存储评测任务的名称、id、类型、评测员等信息
-- 还有一个评测任务收藏表 evaluate_collections，存储哪个用户收藏了哪个评测任务 id

-- 现在我们找出所有被收藏的任务的类型
SELECT name task_name, evaluate_type 
FROM evaluate_tasks, evaluate_collections
WHERE evaluate_tasks.task_id = evaluate_collections.task_id;

-- FROM 后面跟了两个表名，它们就是连接的两个表
-- 注意这里要用完全限定列名，即 表名.列名，否则不知道是哪个表的列
-- WHERE 子句是必要的，不加限制的话返回数据将是笛卡尔积(m * n 行)

表名也可以重命名，简化写法：

SELECT * 
FROM tasks AS T
RIGHT OUTER JOIN collections AS C ON T.task_id = C.task_id;

连接查询有四种：

• 内连接（INNER JOIN） ：返回两个表中匹配的行，即两个表中都存在的记录。
• 左外连接（LEFT OUTER JOIN） ：返回左边表中的全部数据以及右边表中与左边表中的数据匹配的行。如果两个表中没有匹配项，则右边表中的列显示为 NULL。
• 右外连接（RIGHT OUTER JOIN） ：返回右边表中的全部数据以及左边表中与右边表中的数据匹配的行。如果两个表中没有匹配项，则左边表中的列显示为 NULL。
• 全外连接(FULL OUTER JOIN) ：返回左右两边表中的全部数据，并进行匹配。如果左边表中的数据在右边表中没有匹配项，则右边表中的列显示为 NULL。如果右边表中的数据在左边表中没有匹配项，则左边表中的列显示为 NULL。

-- 假设我们有两个表：users 表和 orders 表
-- users 表中存储了所有用户的基本信息，orders 表中存储了所有订单的信息

-- users 表

id | name   | email         
---|--------|---------------
 1 | Alice  | [email protected]
 2 | Bob    | [email protected]
 3 | Charlie| [email protected]
 
-- orders 表

id | user_id | product       
---|---------|---------------
 1 | 1       | iPhone 12
 2 | 1       | MacBook Air
 3 | 2       | iPad Pro  
 

-- 内连接： 
SELECT *
FROM users
INNER JOIN orders
ON users.id = orders.user_id;

-- 结果：
id | name   | email             | id | user_id | product       
---|--------|------------------|----|---------|---------------
 1 | Alice  | [email protected] | 1  | 1       | iPhone 12
 1 | Alice  | [email protected] | 2  | 1       | MacBook Air
 2 | Bob    | [email protected]   | 3  | 2       | iPad Pro  


-- 左外连接： 
SELECT *
FROM users
LEFT OUTER JOIN orders
ON users.id = orders.user_id;

-- 结果：
id | name    | email             | id | user_id | product       
---|---------|------------------|----|---------|---------------
 1 | Alice   | [email protected] | 1  | 1       | iPhone 12
 1 | Alice   | [email protected] | 2  | 1       | MacBook Air
 2 | Bob     | [email protected]   | 3  | 2       | iPad Pro  
 3 | Charlie | [email protected]|NULL|NULL     |NULL          

-- 右外连接： 
SELECT *
FROM users
RIGHT OUTER JOIN orders
ON users.id = orders.user_id;

-- 结果：
id  | name   | email              | id | user_id | product       
----|--------|--------------------|----|---------|-------------
  1 | Alice  | [email protected]  |  1 | 1       | iPhone 12
  1 | Alice  | [email protected]  |  2 | 1       | MacBook Air
  2 | Bob    | [email protected]    |  3 | 2       | iPad Pro  
NULL| NULL   | NULL               |  4 | 3       | iMac

-- 全外连接： 
SELECT *
FROM users
FULL OUTER JOIN orders
ON users.id = orders.user_id;

-- 结果：
id  |  name   | email              | id   | user_id | product       
----|---------|--------------------|------|---------|-------------
1   | Alice   | [email protected]  |  1   | 1       | iPhone 12
1   | Alice   | [email protected]  |  2   | 1       | MacBook Air
2   | Bob     | [email protected]    |  3   | 2       | iPad Pro  
3   | Charlie | [email protected]| NULL | NULL    | NULL
NULL| NULL    | NULL               |  4   | 3       | iMac

INSERT 插入

INSERT INTO <table_name> (column1, column2, ...) VALUES (value1, value2, ...);

-- 推荐写法：指明列，然后声明对应的值，不容易出错
-- 这种写法支持插入一整行或者行的某几列
INSERT INTO users (name, age) VALUES ('xiaoming', 22); -- 字段与值一一对应

-- 插入某些查询的结果
INSERT INTO users(
  user_id,
  user_email,
  user_address)
SELECT user_id, user_email, user_address -- 这里列名不一定要完全一致，会按照顺序一一匹配
FROM users_old;

-- INSERT INTO 插入一行，INSERT SELECT 支持多行插入

-- 复制/备份 一张表
CREATE TABLE users_copy AS SELECT * FROM users;

-- 以上 SELECT 写法支持所有的 SELECT 子句，像 WHERE 和 GROUP BY 啥的

UPDATE 更新

UPDATE <table_name> SET column1=value1, column2=value2, ... WHERE ...;

UPDATE users SET is_vip=1 WHERE name='hewei'; 

-- UPDATE 可以使用子查询，检索出更多的数据

-- 用 NULL 删除某个列
UPDATE users SET address = NULL WHERE name='hewei'; 

DELETE 删除

DELETE FROM <table_name> WHERE ...;

DELETE FROM users WHERE age < 18; -- 删除整行

-- 删除表中所有行
TRUNCATE <table_name>;

Q：如果两个表有连接关系，比如有一个产品表和订单表，它们通过 product_id 建立连接关系，如果要从产品表中删除一个产品，而这个产品在订单表中被用到了，该如何处理？

A：可以通过在表上施加约束来实施引用完整性。

存储过程

上面的示例都是一些非常简单的增删改查，但在实际的业务场景中，执行某个处理可能需要针对许多表的多条 SQL 语句。

针对这种复杂场景，我们如何处理呢？可以创建存储过程。数据库 SQL 语言层面的代码封装与重用。

存储过程就是为以后使用而保存的一条或多条 SQL 语句。可将其视为批文件，虽然它们的作用不仅限于批处理。

❑ 通过把处理封装在一个易用的单元中，可以简化复杂的操作；

❑ 简化对变动的管理；

❑ 存储过程通常以编译过的形式存储，可以提升性能；

好处：简单、安全、高性能。

• 存储过程就是具有名字的一段代码，用来完成一个特定的功能。
• 创建的存储过程保存在数据库的数据字典中。

执行存储过程的语句：EXECUTE

创建存储过程的语句：CREATE PROCEDUR

如何创建：【菜鸟教程】存储过程

事务处理

使用事务处理（transaction processing），通过确保成批的 SQL 操作要么完全执行，要么完全不执行，来维护数据库的完整性。

事务处理用来管理 INSERT、UPDATE 和 DELETE 语句。

一般来说，事务必须满足 ACID 四性，即：

• 原子性（Atomicity，或称不可分割性）：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。如果操作不具有原子性，即可以再分为多个操作，那么当这些操作出现错误或抛出异常时，整个操作就可能不会继续执行下去，而已经进行的操作造成的副作用就可能造成数据更新的丢失或者错误。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• 一致性（Consistency）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。写入的数据必须完全符合所有的预设规则。
• 隔离性（Isolation，又称独立性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• 持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

想要保证事务的原子性，就需要在异常发生时，对已经执行的操作进行回滚，而在 MySQL 中，恢复机制是通过回滚日志 （undo log） 实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后在对数据库中的对应行进行写入。

管理事务的关键在于将 SQL 语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。

事务控制语句

• BEGIN 或 START TRANSACTION 显式地开启一个事务；
• COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
• ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
• SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

savepoint 是在数据库事务处理中实现“子事务”（subtransaction），也称为嵌套事务的方法。事务可以回滚到 savepoint 而不影响 savepoint 创建前的变化, 不需要放弃整个事务。

可以让我们灵活回退。

• RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
• ROLLBACK TO identifier 把事务回滚到标记点；
• SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

BEGIN; -- 开始事务

INSERT INTO products(
  prod_id,
  vend_id,
  prod_name,
  prod_price,
  prod_desc,
  created_at
  ) 
VALUES (
  2,
  'x0s3nkd02',
  '电话抱枕',
  38,
  '张远向快乐出发同款',
  DATE_FORMAT(now(),'%y-%m-%d')
);

UPDATE products SET prod_price = prod_price + 100 WHERE prod_id = 1;

COMMIT; -- 提交事务

TODO：在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

了解 Innodb

约束

关系型数据库存储会分解成很多张表，每个表存储相对独立的数据，利用键来建立从一个表到另一个表的引用。要正确地进行关系数据库的设计，需要保证在表中插入合法数据。

比如我们有两个表 A 和 B，它们通过某个 prod_id 建立关联关系，那我们向表 B 中插入数据时，需要保证引用的数据在 A 中存在。虽然我们可以在插入数据时进行检查，但是这样难免有疏漏，不可靠，那咋办呢？我们可以通过在表上施加约束来实施引用完整性。

主键：唯一且不可修改；

外键：表中的一列，其值必须列在另一表的主键中。

比如我们有一个订单表 orders 和一个用户表 customers，其中 orders 的主键为 order_id，customers 的主键为 customer_id，orders 表通过 customer_id 和 customers 表相关联。

在 orders 表中，customer_id 列不一定唯一，但它的合法值为 customers 表中的主键 customer_id。

CREATE TABLE orders (
  order_id CHAR(50) NOT NULL,
  order_num INTEGER NOT NULL,
  customer_id CHAR(50) NOT NULL REFERENCES customers(customer_id), -- 定义外键
  PRIMARY KEY (order_id)
);

在定义外键后，DBMS 不允许删除在另一个表中具有关联行的行。例如，不能删除关联订单的用户。删除该用户首先需要删除相关的订单。

有的 DBMS 支持称为级联删除（cascading delete）的特性。如果启用，该特性在从一个表中删除行时删除所有相关的数据。例如，如果启用级联删除并且从 customers 表中删除某个用户，则任何关联的订单行也会被自动删除。

唯一约束：保证一列（或一组列）中的数据是唯一的，和主键类似，但唯一约束不可以用来定义外键，可以更新或使用 NULL 值。

索引

索引用来排序数据以加快搜索和排序操作的速度。

就像字典的目录一样，查询汉字会先在目录查找，而不是盲目全书查询。

数据库主键数据总是排序的，这是 DBMS 的工作。因此，按主键检索特定行总是一种快速有效的操作。但如果我们要搜索其他列的数据通常效率不高。

怎么办呢？解决方法是使用索引。可以在一个或多个列上定义索引，使 DBMS 保存其内容的一个排过序的列表。在定义了索引后，DBMS 以使用书的索引类似的方法使用它。DBMS 搜索排过序的索引，找出匹配的位置，然后检索这些行。

MySQL 索引类似于书籍的索引，通过存储指向数据行的指针，可以快速定位和访问表中的特定数据。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

索引虽然能够提高查询性能，但也需要注意以下几点：

• 索引需要占用额外的存储空间；
• 对表进行插入、更新和删除操作时，索引需要维护，可能会影响性能；
• 过多或不合理的索引可能会导致性能下降，因此需要谨慎选择和规划索引。

索引分单列索引和组合索引：

• 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引。
• 组合索引，即一个索引包含多个列。

-- 创建索引
CREATE INDEX index_name 
ON table_name  (column1 [ASC|DESC], column2 [ASC|DESC], ...);
-- index_name 在表中必须唯一
-- ASC 和 DESC（可选），用于指定索引的排序顺序，默认升序（ASC）

CREATE INDEX idx_name 
ON products (prod_name);

-- 添加索引
ALTER TABLE table_name 
ADD INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

ALTER TABLE products
ADD INDEX idx_price (prod_price);

-- 创建表的时候直接指定
CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...,
  INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...)
);

-- 删除索引
DROP INDEX index_name ON table_name;

ALTER TABLE table_name
DROP INDEX index_name;

-- 唯一索引：确保索引中的值是唯一的，不允许有重复值
CREATE UNIQUE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

索引常见的数据结构包括：B tree、B+ tree、二叉树、红黑树、哈希表...

数据库安全

用户授权

需要用户授权和身份认证，保证他是有权用户，允许执行他要执行的操作。

SQL 注入

永远不要信任用户的输入，我们必须认定用户输入的数据都是不安全的，我们都需要对用户输入的数据进行过滤处理。

假设有一个登录系统，用户通过输入用户名和密码进行身份验证：

SELECT * FROM users WHERE username = 'input_username' AND password = 'input_password';

如果以输入类似于以下内容的用户名：

' OR '1'='1'; --

我们没有防范措施的话，SQL 查询会变成：

SELECT * FROM users WHERE username = '' OR '1'='1'; --' AND password = 'input_password';

这会使查询返回所有用户，因为 1=1 总是为真。

防范 SQL 注入：

• 使用参数化查询或预编译语句： 使用参数化查询（Prepared Statements）可以有效防止 SQL 注入，因为它们在执行查询之前将输入数据与查询语句分离。
• 输入验证和转义： 对用户输入进行适当的验证，并使用合适的转义函数（如mysqli_real_escape_string）来处理输入，以防止恶意注入。
• 最小权限原则： 给予数据库用户最小的权限，确保它们只能执行必要的操作，以降低潜在的损害。
• 使用 ORM 框架： 使用对象关系映射（ORM）框架可以帮助抽象 SQL 查询，从而降低 SQL 注入的风险。
• 禁用错误消息显示： 在生产环境中，禁用显示详细的错误消息，以防止攻击者获取有关数据库结构的敏感信息。

SQL 数据类型

OK，到此你已经学会了基本的增删改查，应该能应对 80% 的场景了，熟能生巧！

但是当你接手项目，却发现这里面一句 SQL 都没有？！

哦，原来是我们可以不用再这么傻乎乎的写 SQL 了，有一个叫 ORM 的东西可以帮我们更高效、简洁地访问/处理数据库数据，不需要和复杂的 SQL 语句打交道！

所以还得学一下 ORM ，由于我们项目中用的是 Golang，所以直接学习基于 Golang 的 ORM 库 GORM。

GORM

ORM（对象-关系映射，Object-Relational Mapping），它是个啥呢？一个连接面向对象语言和关系数据库的桥梁，可以让我们更高效、简洁地访问/处理数据库数据。

💡 用一个类（结构体）表示一张表，类中的属性就表示表的字段，类的实例化对象表示一条记录。

嗯，很符合我们 OOP 的编程习惯。

GORM [ɡɔːm] 是基于 Golang 的 ORM 库工具。

优点：提高开发效率；缺点：执行性能差、灵活性差、弱化 SQL 能力；

适合内部使用的项目，不适合大型项目。

模型定义

在 GORM 中，操作数据库需要预先定义模型， 用来和数据表做映射。比如定义一个 User 结构体，

这个结构体就是数据模型。

底层使用 Golang 的 database 标准库，利用反射原理，执行读写操作时，将结构体翻译为 SQL 语句，并转换为对应的模型。

// 用 User 类表示一张用户信息表
type User struct {
  ID           uint
  Name         string
  Email        *string
  Age          uint8
  Birthday     *time.Time
  MemberNumber sql.NullString
  ActivatedAt  sql.NullTime
  CreatedAt    time.Time
  UpdatedAt    time.Time
}

// 模型标签，做一些额外操作
type User struct {
  ID           uint `gorm:"primaryKey"` // 指定主键，当然了 id 会默认为主键
  Name         string
  Email        *string
  Age          uint8
  Birthday     *time.Time
  MemberNumber sql.NullString
  ActivatedAt  sql.NullTime `gorm:"-"` // 忽略该字段，不会在表中使用
  CreatedAt    time.Time `gorm:"column:createtime"` // 指定该字段在表中为 createtime
  UpdatedAt    time.Time 
}

// 表名映射
1. 复数表名，如 User 会默认映射为 users
2. 实现 Tabler 接口，基于 TableName 映射
type Tabler interface {
  TableName() string
}

func (User) TableName() string {
  return "users_info"
}

3. 动态表名 Scopes

// 字段添加前缀 `gorm: embedded; embeddedPrefix:xxx`

连接数据库

要处理数据库中的数据，第一步当然是连接相关数据库了，需要创建 gorm 数据库实例，进行初始化：

func main() {
  // 参考 https://github.com/go-sql-driver/mysql#dsn-data-source-name 获取详情
  dsn := "user:pass@tcp(127.0.0.1:3306)/dbname?charset=utf8mb4&parseTime=True&loc=Local"
  db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
}

// bytedgorm
DB, err := gorm.Open(
  bytedgorm.MySQL("p.s.m", "dbname").With(func(conf *bytedgorm.DBConfig) {
    // 通过 conf 选项可修改数据库连接的配置信息
    conf.ReadTimeout = 2*time.Second
  // WithReadReplicas 开启读写分离, 将使用inf.lidar.data_write 做为写db, inf.lidar.data_read 做为读db, 不开启时将使用 inf.lidar.data_write 进行读写
  }).WithReadReplicas(),
  // 配置连接池的信息
  bytedgorm.ConnPool{MaxIdleConns: 200, MaxOpenConns: 200},
  // 使用订制的 logs 做为 logger
  bytedgorm.Logger{LogLevel: logger.Info},
  // 允许压测模式，在使用压测平台进行压力测试时，会使用影子表进行查询等
  // 线上谨慎使用 Info 级别, 可能会导致执行的 SQL 泄漏,可以使用默认值 Error 或者 Slient
  bytedgorm.WithStressTestSupport(),
  // 使用单数作为表名
  bytedgorm.WithSingularTable(),
)

业务使用举例：

func RDSInit() {
    var err error
    // biz.GetConfig().RDS.PSM 你的业务 psm
    // biz.GetConfig().RDS.DbName 数据库名
    dbConfig := bytedgorm.MySQL(biz.GetConfig().RDS.PSM, biz.GetConfig().RDS.DbName).WithReadReplicas()
    dbConfig.ReadTimeout = 600 * time.Second
dbConfig.WriteTimeout = 600 * time.Second
rdsClient, err = gorm.Open(
       dbConfig,
       bytedgorm.WithDefaults(),
       bytedgorm.Logger{LogLevel: logger.Error, IgnoreRecordNotFoundError: true},
    )
    if err != nil {
       panic(err)
    }
}

CRUD

连接数据库之后，就可以进行增删改查了。

Create

user := User{Name: "hewei", Email: "[email protected]"}

result := db.Create(&user) // 通过数据的指针来创建

user.ID // 这条记录的主键
result.Error // 错误信息

// 创建多条记录
users := []*User{
  User{Name: "Jinzhu", Email: "[email protected]"},
  User{Name: "Jackson", Email: "[email protected]"},
}

result := db.Create(users) // 传递切片以插入多行数据

// 指定字段
db.Select("Name", "Email").Create(&user)

READ

// Get all records
result := db.Find(&users)

// 获取第一条记录（主键升序）
db.First(&user)
// SELECT * FROM users ORDER BY id LIMIT 1;

// 获取一条记录，没有指定排序字段
db.Take(&user)
// SELECT * FROM users LIMIT 1;

// 获取最后一条记录（主键降序）
db.Last(&user)
// SELECT * FROM users ORDER BY id DESC LIMIT 1;

result := db.First(&user)
result.RowsAffected // 返回找到的记录数
result.Error // returns error or nil

// 检查 ErrRecordNotFound 错误
errors.Is(result.Error, gorm.ErrRecordNotFound)

// Get first matched record
db.Where("name = ?", "hewei").First(&user)
// SELECT * FROM users WHERE name = 'hewei' ORDER BY id LIMIT 1;

// Get all matched records
db.Where("name <> ?", "hewei").Find(&users)
// SELECT * FROM users WHERE name <> 'hewei';

UPDATE

// 根据条件更新, User 的 ID 是 `111`
db.Model(&user).Update("name", "hello")

// 根据条件和 model 的值进行更新
db.Model(&user).Where("active = ?", true).Update("name", "hello")

// 根据 `struct` 更新属性，只会更新非零值的字段
db.Model(&user).Updates(User{Name: "hello", Age: 18, Active: false})

DELETE

// Email 的 ID 是 `10`
db.Delete(&email)
// DELETE from emails where id = 10;

// 带额外条件的删除
db.Where("name = ?", "hewei").Delete(&email)
// DELETE from emails where id = 10 AND name = "hewei";

// 根据主键删除
db.Delete(&User{}, 10)
// DELETE FROM users WHERE id = 10;

// 批量删除
var users = []User{{ID: 1}, {ID: 2}, {ID: 3}}
db.Delete(&users)
// DELETE FROM users WHERE id IN (1,2,3);

如果模型包含了 gorm.DeletedAt 字段，模型将会自动获得软删除的能力。调用Delete时，GORM 并不会从数据库中删除该记录，而是将该记录的DeleteAt设置为当前时间，而后的一般查询方法将无法查找到此条记录。

// 可以使用 Unscoped 来查询到被软删除的记录
db.Unscoped().Where("age = 20").Find(&users)
// SELECT * FROM users WHERE age = 20;

// 可以使用 Unscoped来永久删除匹配的记录
db.Unscoped().Delete(&order)
// DELETE FROM orders WHERE id=10;

事务

在 GORM 中，必然也有「事务」的概念，是用来确保对于多个数据库操作能够像一个单独的操作那样被执行，或者在一个操作失败时回滚到一个已知状态的机制。

打开数据库并开始事务：

// 连接数据库并开启事务
tx := db.Begin()

// 进行操作
tx.Create(&User{Name: "hewei", Email: "[email protected]"})
tx.Delete(&Order{ID: 6})

// 提交事务
tx.commit()

// 回滚事务
tx.Rollback()

进阶

待了解

1、一条 SQL 语句的执行，中间发生了什么？

2、索引是如何实现的？多种引擎的实现区别？聚族索引，非聚族索引，二级索引，唯一索引、最左匹配原则等等。

3、事务相关：例如事务的隔离是如何实现的？事务是如何保证原子性？MVCC 的实现原理。

4、各种锁相关：例如表锁，行锁，间隙锁，共享锁，排他锁。

5、日志相关：redolog，binlog，undolog，这些日志的实现原理，为了解决怎么问题？

6、数据库的主从备份、如何保证数据不丢失、如何保证高可用等等。

7、一些故障排查的命令，例如慢查询，SQL 的执行计划，索引统计的刷新等。

参考资料

📖《SQL 必知必会》