36条MySQL实用小技巧
SQL面试专栏
前天分享了50条MySQL常用脚本，有小伙伴说岳哥怎么突然这么卷？
实际上一直都有在学习，只是公众号的算法机制让很多人刷不到，看得人比较少，这才将这些内容分享到各个群和朋友圈，希望能让更多人看到。
好了咱们今天继续分享MySQL中一些比较实用的小技巧。
1、使用REPLACE INTO来插入或替换数据，避免先删除再插入的操作。
REPLACE INTO table_name (id, name) VALUES (1, 'John');注释：如果id为1的记录已存在，则该记录将被更新；否则，将插入一条新记录。这种方法避免了先删除再插入的过程。
2、通过SET GLOBAL sql_mode=''来临时禁用SQL严格模式。
SET GLOBAL sql_mode='';注释：这将在当前会话中禁用SQL严格模式，使得一些非标准的SQL语句能够执行。
3、利用EXPLAIN语句来分析查询性能，找出潜在的性能瓶颈。
EXPLAIN SELECT * FROM table_name WHERE id = 1;注释：EXPLAIN语句可帮助你了解查询的执行计划，帮助优化查询性能。
4、使用LOAD DATA INFILE快速导入大量数据。
LOAD DATA INFILE 'file_path' INTO TABLE table_name;注释：通过LOAD DATA INFILE可以快速地将文件中的数据导入数据库表中，适用于大量数据的导入操作。
5、利用分区表来优化大表的查询性能。
CREATE TABLE sales (
id INT NOT NULL,
sale_date DATE,
amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(sale_date)) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN (2010),
PARTITION p3 VALUES LESS THAN MAXVALUE
);注释：通过分区表可以将大表拆分为更小的部分，提高查询效率，尤其是针对时间范围查询时。
6、使用批量插入来提高插入数据的效率。
INSERT INTO table_name (id, name) VALUES
(1, '张三'),
(2, '李四'),
(3, '王五');注释：一次性插入多行数据可以减少插入操作的开销，提高效率。
7、利用用户自定义变量简化复杂查询。
SET @min_salary = 50000;
SELECT name, salary FROM employees WHERE salary > @min_salary;注释：通过自定义变量可以在查询中使用变量，简化复杂查询的编写和维护。
8、通过分析表的索引使用情况来优化查询性能。
SHOW INDEX FROM table_name;注释：查看表的索引情况可以帮助你评估哪些索引被查询使用，哪些可以优化或重建。
9、使用MySQL事件调度器来执行定时任务。
CREATE EVENT my_event
ON SCHEDULE EVERY 1 HOUR
DO
UPDATE table_name SET status = 'expired'
WHERE expiration_date < NOW();注释：通过事件调度器可以定时执行MySQL语句，执行定时任务如数据清理、自动化操作等。
10、利用全文索引提高搜索效率。
CREATE FULLTEXT INDEX idx_title ON articles(title);
SELECT * FROM articles
WHERE MATCH(title) AGAINST('search keyword');注释：全文索引可加速对文本内容的搜索，提高检索效率。
11、使用ON DUPLICATE KEY UPDATE来插入新数据或更新现有数据。
INSERT INTO table_name (id, name)
VALUES (1, '张三') ON DUPLICATE KEY UPDATE name = '张三';注释：如果插入数据的主键已存在，将会执行更新操作而不是插入新数据。
12、利用VALUES()函数来在INSERT语句中引用插入的值。
INSERT INTO table_name (id, name)
VALUES (1, 'Alice'), (2, VALUES(name));注释：VALUES()函数可以引用正在插入的值，方便实现一次插入多个记录且其中某些值相同的需求。
13、使用SHOW PROFILE来查看查询的性能分析结果。
SET PROFILING = 1;
SELECT * FROM table_name;
SHOW PROFILES;注释：SHOW PROFILE可以显示查询执行的详细性能信息，帮助优化查询。
14、利用TRIGGERS来触发特定操作。
CREATE TRIGGER before_insert_trigger
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
SET NEW.date_created = NOW();
END;注释：TRIGGERS可用于在特定事件发生时自动执行一些操作，如更新其他列的值等。
15、使用MEMORY引擎来创建内存表，加快某些小型数据的访问速度。
CREATE TABLE memory_table ENGINE=MEMORY
AS
SELECT * FROM table_name;注释：MEMORY引擎将表保存在内存中，适用于缓存或临时性数据的查询。
16、使用ROW_NUMBER()函数模拟MySQL中的行号（Row_Number）功能。
SELECT
@rownumber := @rownumber + 1 AS rownumber,
col1, col2
FROM
table_name, (SELECT @rownumber := 0) AS t;注释：通过设置变量并自增来模拟行号功能，可以在结果集中为每行分配唯一标识符。
17、利用IGNORE选项忽略插入数据中的重复值。
INSERT IGNORE INTO table_name (id, name)
VALUES (1, '张三'), (1, '李四');注释：当插入数据中有重复值时，使用IGNORE选项可以忽略重复值而继续插入其他数据。
18、使用BIN()函数对字段进行二进制字符串转换。
SELECT BIN(10); -- 输出 '1010'注释：BIN()函数可以将数字转换为二进制字符串表示形式。
19、利用GROUP_CONCAT()函数将多行数据合并成一行并用特定分隔符分隔。
SELECT id, GROUP_CONCAT(name SEPARATOR ', ') AS names
FROM table_name GROUP BY id;注释：GROUP_CONCAT()函数可以将多行数据合并成一行，并通过指定的分隔符进行分隔。
20、使用JSON数据类型来存储和查询JSON格式的数据。
CREATE TABLE json_data (
id INT PRIMARY KEY,
data JSON
);
INSERT INTO json_data (id, data)
VALUES (1, '{"name": "张三", "age": 30}');
SELECT data->'$.name' AS name
FROM json_data WHERE id = 1;注释：JSON数据类型适用于存储和处理具有结构化格式的数据，提供了方便的JSON操作功能。
21、使用CHECK约束实现数据完整性验证。
CREATE TABLE employees (
id INT PRIMARY KEY,
age INT,
CHECK (age >= 18)
);注释：CHECK约束可确保数据符合特定条件，保证数据完整性。
22、利用EXISTS子查询来判断子查询是否返回结果。
SELECT * FROM table_name t
WHERE EXISTS
(SELECT 1 FROM other_table o
WHERE o.id = t.id);注释：利用EXISTS子查询可以根据子查询是否返回结果筛选主查询的结果。
23、使用RAND()函数生成随机数。
SELECT RAND(); -- 返回一个0到1之间的随机数注释：RAND()函数可用于生成随机数，适用于需要随机值的场景。
24、利用ENUM数据类型限制列的取值范围。
CREATE TABLE students (
id INT PRIMARY KEY,
gender ENUM('Male', 'Female')
);注释：ENUM数据类型可以定义列的可选取值，限制输入的数据在预定义的范围内。
25、使用DATE_FORMAT()函数将日期格式化为指定的字符串形式。
SELECT DATE_FORMAT('2024-04-19 12:00:00', '%Y-%m-%d'); -- 输出 '2024-04-19'注释：DATE_FORMAT()函数可以将日期格式化为指定的字符串形式，方便日期显示和处理。
26、利用SUBSTRING_INDEX函数提取字符串中的子串。
SELECT SUBSTRING_INDEX('apple,banana,orange', ',', 2);
-- 输出 'apple,banana'注释：SUBSTRING_INDEX函数可以根据指定的分隔符提取字符串中的子串。
27、使用CONVERT()函数进行数据类型转换。
SELECT CONVERT('123', SIGNED);
-- 将字符串'123'转换为有符号整数注释：CONVERT()函数可用于将数据从一种类型转换为另一种类型。
28、使用CASE语句实现条件逻辑。
SELECT
CASE
WHEN marks >= 90 THEN 'A'
WHEN marks >= 80 THEN 'B'
ELSE 'C'
END AS grade
FROM student_marks;注释：CASE语句可根据条件执行不同的逻辑，类似于编程语言中的条件语句。
29、利用HAVING子句过滤聚合函数结果。
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING avg_salary > 50000;注释：HAVING子句用于在执行GROUP BY后过滤聚合函数结果。
30、使用LIMIT和OFFSET分页查询结果集。
SELECT * FROM products LIMIT 10 OFFSET 20;注释：LIMIT用于限制结果集返回的行数，OFFSET用于指定起始位置。
31、使用TRANSACTION来确保一组SQL操作要么全部执行成功，要么都不执行。
START TRANSACTION;
INSERT INTO table1 (col1, col2) VALUES (val1, val2);
UPDATE table2 SET col3 = val3 WHERE col4 = val4;
COMMIT;注释：TRANSACTION可以保证一组SQL操作的原子性，如果其中任何一个操作失败，则整个操作将回滚。
32、使用临时表（Temporary Table）临时存储中间结果。
CREATE TEMPORARY TABLE temp_table AS
SELECT * FROM orders
WHERE order_date = '2024-01-01';注释：临时表在当前会话中存在，会话结束时自动删除，适合存储临时数据或中间计算结果。
33、使用存储点（Savepoint）在事务中实现部分回滚。
START TRANSACTION;
INSERT INTO table1 VALUES (1, 'A');
SAVEPOINT sp1;
INSERT INTO table2 VALUES (2, 'B');
ROLLBACK TO sp1;
COMMIT;注释：存储点可以将事务分段处理，部分回滚受影响的操作
34、使用MySQL的自动递增主键（Auto Increment Primary Key）来自动生成唯一标识符：
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(50)
);注释：自动递增主键可确保每行数据都有唯一的标识符，方便管理和检索。
35、使用备份和恢复策略来保障数据的安全性和可用性，定期进行备份并测试恢复流程：
mysqldump -u [username] -p [database_name] > backup.sql
mysql -u [username] -p [database_name] < backup.sql注释：备份与恢复策略是防止数据丢失或损坏的重要措施，保障数据的可持续运营。
36、监控数据库的系统资源利用情况，包括CPU、内存、磁盘等，及时调整配置以应对系统负载
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW ENGINE INNODB STATUS;注释：监控系统资源利用情况可帮助发现潜在瓶颈并优化数据库性能。