MySQL 实用指南：命令、技巧与最佳实践

MySQL Practical Guide: Commands, Tips and Best Practices

2017-12-20 13:31:00 #MySQL #Database #SQL

本文是一份全面的 MySQL 实用指南，涵盖从环境配置到高级特性的完整内容。包括安装配置、数据库操作、用户权限管理、备份恢复、性能优化、事务管理、常用函数、视图存储过程等常用操作和最佳实践。适合 MySQL 开发者和数据库管理员作为日常参考手册使用。

一、环境配置

1.1 安装与启动

Ubuntu/Debian 系统

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 安装 MySQL
sudo apt-get update
sudo apt-get install mysql-client mysql-server

# 启动/停止/重启服务
sudo systemctl start mysql
sudo systemctl stop mysql
sudo systemctl restart mysql
sudo systemctl status mysql

# 或者使用 service 命令
sudo service mysql start
sudo service mysql stop
sudo service mysql restart

CentOS/RHEL 系统

1
2
3
4
5
6
# 安装 MySQL
sudo yum install mysql-server

# 启动服务
sudo systemctl start mysqld
sudo systemctl enable mysqld  # 设置开机自启

macOS 系统

1
2
3
4
5
# 使用 Homebrew 安装
brew install mysql

# 启动服务
brew services start mysql

1.2 配置文件

1
2
3
4
5
6
7
8
# 默认配置文件位置
/etc/my.cnf          # Linux 通用
/etc/mysql/my.cnf    # Debian/Ubuntu
/usr/local/etc/my.cnf # macOS (Homebrew)
~/.my.cnf            # 用户级配置

# 查看 MySQL 配置
mysql --help | grep "Default options" -A 1

1.3 初始配置

1
2
3
4
5
6
7
8
9
10
11
# 首次登录（MySQL 8.0+）
sudo mysql -u root

# 为 root 用户设置密码
mysqladmin -u root -p password 'newpassword'

# 或者使用 SQL 命令
ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';

# 测试连接
mysql -u root -p

1.4 安全配置

1
2
3
4
5
6
7
8
# 运行安全配置向导（MySQL 5.7+）
sudo mysql_secure_installation

# 该向导会引导你完成：
# - 设置 root 密码
# - 移除匿名用户
# - 禁止 root 远程登录
# - 移除测试数据库

二、数据库操作

2.1 数据库管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 查看所有数据库
SHOW DATABASES;

-- 查看当前使用的数据库
SELECT DATABASE();

-- 创建数据库（指定字符集和排序规则）
CREATE DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 创建数据库（简化写法）
CREATE DATABASE dbname;

-- 删除数据库
DROP DATABASE dbname;

-- 删除数据库（如果存在）
DROP DATABASE IF EXISTS dbname;

-- 使用数据库
USE dbname;

-- 查看数据库创建语句
SHOW CREATE DATABASE dbname;

-- 修改数据库字符集
ALTER DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2.2 表操作

查看表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 查看当前数据库的所有表
SHOW TABLES;

-- 查看表结构
DESCRIBE tablename;
-- 或简写
DESC tablename;

-- 查看表创建语句
SHOW CREATE TABLE tablename;

-- 查看表的详细信息
SHOW TABLE STATUS LIKE 'tablename';

-- 查看表的索引
SHOW INDEX FROM tablename;

创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 基本创建表
CREATE TABLE tablename (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    age INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建表（如果不存在）
CREATE TABLE IF NOT EXISTS tablename (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- 从查询结果创建表
CREATE TABLE new_table AS SELECT * FROM old_table WHERE condition;

修改表结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 添加列
ALTER TABLE tablename ADD COLUMN columnname DATATYPE [NOT NULL] [DEFAULT value];

-- 添加列（指定位置）
ALTER TABLE tablename ADD COLUMN columnname DATATYPE AFTER existing_column;
ALTER TABLE tablename ADD COLUMN columnname DATATYPE FIRST;

-- 删除列
ALTER TABLE tablename DROP COLUMN columnname;

-- 修改列（可以修改名称、类型、约束）
ALTER TABLE tablename CHANGE oldcolumnname newcolumnname DATATYPE;

-- 修改列类型（不改变名称）
ALTER TABLE tablename MODIFY columnname NEW_DATATYPE;

-- 重命名表
ALTER TABLE oldname RENAME TO newname;
-- 或
RENAME TABLE oldname TO newname;

-- 删除表
DROP TABLE tablename;

-- 删除表（如果存在）
DROP TABLE IF EXISTS tablename;

-- 清空表数据（保留表结构）
TRUNCATE TABLE tablename;

2.3 索引操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 创建索引
CREATE INDEX index_name ON tablename(columnname);

-- 创建唯一索引
CREATE UNIQUE INDEX index_name ON tablename(columnname);

-- 创建复合索引
CREATE INDEX index_name ON tablename(column1, column2);

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

-- 查看索引
SHOW INDEX FROM tablename;

三、数据操作

3.1 插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 插入单条数据（指定所有列）
INSERT INTO tablename VALUES(value1, value2, value3, ...);

-- 插入单条数据（指定列）
INSERT INTO tablename(column1, column2) VALUES(value1, value2);

-- 插入多条数据
INSERT INTO tablename(column1, column2) VALUES
    (value1, value2),
    (value3, value4),
    (value5, value6);

-- 从查询结果插入
INSERT INTO tablename1(column1, column2) 
SELECT column3, column4 FROM tablename2 WHERE condition;

-- 插入或更新（ON DUPLICATE KEY UPDATE）
INSERT INTO tablename(id, name) VALUES(1, 'John')
ON DUPLICATE KEY UPDATE name = 'John';

-- 替换数据（REPLACE INTO）
REPLACE INTO tablename(id, name) VALUES(1, 'John');

3.2 查询数据

基本查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
-- 查询所有列
SELECT * FROM tablename;

-- 查询指定列
SELECT column1, column2 FROM tablename;

-- 条件查询
SELECT * FROM tablename WHERE columnname = value;
SELECT * FROM tablename WHERE columnname > value;
SELECT * FROM tablename WHERE columnname != value;
SELECT * FROM tablename WHERE columnname <> value;

-- NULL 值判断
SELECT * FROM tablename WHERE columnname IS NULL;
SELECT * FROM tablename WHERE columnname IS NOT NULL;

-- 多条件查询（AND/OR）
SELECT * FROM tablename WHERE condition1 AND condition2;
SELECT * FROM tablename WHERE condition1 OR condition2;

-- IN 操作符
SELECT * FROM tablename WHERE columnname IN (value1, value2, value3);
SELECT * FROM tablename WHERE columnname IN (SELECT column FROM other_table);

-- NOT IN
SELECT * FROM tablename WHERE columnname NOT IN (value1, value2);

-- BETWEEN 操作符
SELECT * FROM tablename WHERE columnname BETWEEN value1 AND value2;
SELECT * FROM tablename WHERE columnname NOT BETWEEN value1 AND value2;

-- LIKE 模糊查询
SELECT * FROM tablename WHERE columnname LIKE 'pattern';
-- 'abc'     - 精确匹配
-- '%abc'    - 以 abc 结尾
-- 'abc%'    - 以 abc 开头
-- '%abc%'   - 包含 abc
-- '_abc'    - 单个字符后跟 abc

-- 去重查询
SELECT DISTINCT columnname FROM tablename;

-- 排序
SELECT * FROM tablename ORDER BY columnname ASC;   -- 升序
SELECT * FROM tablename ORDER BY columnname DESC;  -- 降序
SELECT * FROM tablename ORDER BY column1 ASC, column2 DESC;

-- 限制结果数量
SELECT * FROM tablename LIMIT 10;                    -- 前 10 条
SELECT * FROM tablename LIMIT 10 OFFSET 20;         -- 跳过 20 条，取 10 条
SELECT * FROM tablename LIMIT 20, 10;               -- 同上（简写）

聚合函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- COUNT - 计数
SELECT COUNT(*) FROM tablename;
SELECT COUNT(columnname) FROM tablename;
SELECT COUNT(DISTINCT columnname) FROM tablename;

-- SUM - 求和
SELECT SUM(columnname) FROM tablename;

-- AVG - 平均值
SELECT AVG(columnname) FROM tablename;

-- MAX/MIN - 最大值/最小值
SELECT MAX(columnname) FROM tablename;
SELECT MIN(columnname) FROM tablename;

-- GROUP BY - 分组
SELECT column1, COUNT(*) FROM tablename GROUP BY column1;
SELECT column1, SUM(column2) FROM tablename GROUP BY column1;

-- HAVING - 分组后过滤
SELECT column1, COUNT(*) as cnt 
FROM tablename 
GROUP BY column1 
HAVING cnt > 10;

连接查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 内连接（INNER JOIN）
SELECT * FROM table1 
INNER JOIN table2 ON table1.id = table2.id;

-- 左连接（LEFT JOIN）
SELECT * FROM table1 
LEFT JOIN table2 ON table1.id = table2.id;

-- 右连接（RIGHT JOIN）
SELECT * FROM table1 
RIGHT JOIN table2 ON table1.id = table2.id;

-- 全外连接（FULL OUTER JOIN）- MySQL 不支持，可用 UNION 实现
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id
UNION
SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;

-- 自连接
SELECT a.name, b.name FROM employees a, employees b 
WHERE a.manager_id = b.id;

子查询

1
2
3
4
5
6
7
8
9
10
11
-- 标量子查询
SELECT * FROM tablename WHERE column = (SELECT column FROM other_table LIMIT 1);

-- 行子查询
SELECT * FROM tablename WHERE (column1, column2) = (SELECT col1, col2 FROM other_table);

-- 列子查询
SELECT * FROM tablename WHERE column IN (SELECT column FROM other_table);

-- EXISTS 子查询
SELECT * FROM tablename WHERE EXISTS (SELECT 1 FROM other_table WHERE condition);

3.3 更新数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 更新单列
UPDATE tablename SET columnname = value WHERE condition;

-- 更新多列
UPDATE tablename 
SET column1 = value1, column2 = value2 
WHERE condition;

-- 使用表达式更新
UPDATE tablename SET columnname = columnname + 1 WHERE condition;

-- 使用子查询更新
UPDATE tablename 
SET columnname = (SELECT column FROM other_table WHERE condition)
WHERE condition;

-- 限制更新数量
UPDATE tablename SET columnname = value WHERE condition LIMIT 10;

3.4 删除数据

1
2
3
4
5
6
7
8
9
10
11
12
-- 删除数据
DELETE FROM tablename WHERE condition;

-- 删除所有数据
DELETE FROM tablename;

-- 限制删除数量
DELETE FROM tablename WHERE condition LIMIT 10;

-- 使用子查询删除
DELETE FROM tablename 
WHERE columnname IN (SELECT column FROM other_table WHERE condition);

四、用户与权限管理

4.1 用户管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 查看所有用户
SELECT user, host FROM mysql.user;

-- 创建用户
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'username'@'%' IDENTIFIED BY 'password';  -- 允许任意主机

-- 删除用户
DROP USER 'username'@'localhost';

-- 修改用户密码
ALTER USER 'username'@'localhost' IDENTIFIED BY 'newpassword';

-- 重命名用户
RENAME USER 'oldname'@'localhost' TO 'newname'@'localhost';

4.2 权限管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 授予权限
GRANT SELECT, INSERT ON dbname.* TO 'username'@'localhost';
GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost';

-- 常用权限
-- SELECT, INSERT, UPDATE, DELETE - 数据操作权限
-- CREATE, DROP, ALTER - 结构操作权限
-- INDEX - 索引权限
-- REFERENCES - 外键权限
-- ALL PRIVILEGES - 所有权限

-- 撤销权限
REVOKE SELECT ON dbname.* FROM 'username'@'localhost';

-- 查看用户权限
SHOW GRANTS FOR 'username'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

4.3 角色管理（MySQL 8.0+）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 创建角色
CREATE ROLE 'role_name';

-- 授予角色权限
GRANT SELECT ON dbname.* TO 'role_name';

-- 将角色授予用户
GRANT 'role_name' TO 'username'@'localhost';

-- 激活角色
SET DEFAULT ROLE 'role_name' TO 'username'@'localhost';
-- 或
SET ROLE 'role_name';

-- 查看角色
SELECT * FROM mysql.roles_mapping;

五、备份与恢复

5.1 备份数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 备份单个数据库
mysqldump -u username -p dbname > backup.sql

# 备份所有数据库
mysqldump -u username -p --all-databases > all_databases.sql

# 备份指定数据库（多个）
mysqldump -u username -p --databases db1 db2 > backup.sql

# 只备份表结构
mysqldump -u username -p --no-data dbname > structure.sql

# 只备份数据
mysqldump -u username -p --no-create-info dbname > data.sql

# 压缩备份
mysqldump -u username -p dbname | gzip > backup.sql.gz

# 备份时指定字符集
mysqldump -u username -p --default-character-set=utf8mb4 dbname > backup.sql

5.2 恢复数据库

1
2
3
4
5
6
7
8
# 恢复数据库
mysql -u username -p dbname < backup.sql

# 恢复所有数据库
mysql -u username -p < all_databases.sql

# 恢复压缩备份
gunzip < backup.sql.gz | mysql -u username -p dbname

5.3 增量备份

1
2
3
4
5
6
7
8
9
10
11
12
# 启用二进制日志
# 在 my.cnf 中添加：
# log-bin=mysql-bin
# binlog-format=ROW

# 查看二进制日志
mysqlbinlog mysql-bin.000001

# 恢复指定时间点的数据
mysqlbinlog --start-datetime="2023-01-01 00:00:00" \
            --stop-datetime="2023-01-02 00:00:00" \
            mysql-bin.000001 | mysql -u username -p

六、性能优化

6.1 查询优化

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 使用 EXPLAIN 分析查询计划
EXPLAIN SELECT * FROM tablename WHERE columnname = 'value';

-- 查看慢查询日志
SHOW VARIABLES LIKE 'slow_query%';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- 记录超过 2 秒的查询

-- 查看正在执行的查询
SHOW PROCESSLIST;

-- 终止查询
KILL query_id;

6.2 索引优化

1
2
3
4
5
6
7
8
9
10
11
-- 分析表（更新索引统计信息）
ANALYZE TABLE tablename;

-- 优化表（整理碎片）
OPTIMIZE TABLE tablename;

-- 检查表
CHECK TABLE tablename;

-- 修复表
REPAIR TABLE tablename;

6.3 配置优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# my.cnf 常用优化配置

[mysqld]
# 连接数
max_connections = 200

# 查询缓存（MySQL 8.0 已移除）
# query_cache_size = 64M

# InnoDB 缓冲池
innodb_buffer_pool_size = 1G

# 慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

七、事务管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- 开启事务
START TRANSACTION;
-- 或
BEGIN;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 设置保存点
SAVEPOINT savepoint_name;

-- 回滚到保存点
ROLLBACK TO savepoint_name;

-- 设置事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 查看当前隔离级别
SELECT @@transaction_isolation;

八、常用函数

8.1 字符串函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- 字符串连接
SELECT CONCAT('Hello', ' ', 'World');
SELECT CONCAT_WS('-', '2023', '01', '01');

-- 字符串长度
SELECT LENGTH('Hello');
SELECT CHAR_LENGTH('Hello');  -- 字符数（支持多字节）

-- 子字符串
SELECT SUBSTRING('Hello World', 1, 5);  -- 'Hello'
SELECT LEFT('Hello', 3);   -- 'Hel'
SELECT RIGHT('Hello', 3);  -- 'llo'

-- 大小写转换
SELECT UPPER('hello');  -- 'HELLO'
SELECT LOWER('HELLO');  -- 'hello'

-- 去除空格
SELECT TRIM('  hello  ');   -- 'hello'
SELECT LTRIM('  hello');    -- 'hello'
SELECT RTRIM('hello  ');    -- 'hello'

-- 替换
SELECT REPLACE('Hello World', 'World', 'MySQL');

-- 查找位置
SELECT LOCATE('World', 'Hello World');  -- 7
SELECT POSITION('World' IN 'Hello World');  -- 7

8.2 数值函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 四舍五入
SELECT ROUND(3.14159, 2);  -- 3.14

-- 向上取整
SELECT CEIL(3.14);  -- 4

-- 向下取整
SELECT FLOOR(3.14);  -- 3

-- 绝对值
SELECT ABS(-10);  -- 10

-- 随机数
SELECT RAND();

-- 幂运算
SELECT POW(2, 3);  -- 8

8.3 日期时间函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 当前日期时间
SELECT NOW();           -- 2023-01-01 12:00:00
SELECT CURDATE();       -- 2023-01-01
SELECT CURTIME();       -- 12:00:00

-- 日期格式化
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日');

-- 日期计算
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);
SELECT DATEDIFF('2023-01-02', '2023-01-01');  -- 1

-- 提取日期部分
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

8.4 条件函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- IF 函数
SELECT IF(1 > 0, 'Yes', 'No');  -- 'Yes'

-- CASE 语句
SELECT 
    CASE 
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        ELSE 'D'
    END AS grade
FROM students;

-- IFNULL / COALESCE
SELECT IFNULL(NULL, 'Default');  -- 'Default'
SELECT COALESCE(NULL, NULL, 'Value');  -- 'Value'

九、视图与存储过程

9.1 视图

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 创建视图
CREATE VIEW view_name AS 
SELECT column1, column2 FROM tablename WHERE condition;

-- 查看视图
SHOW CREATE VIEW view_name;

-- 删除视图
DROP VIEW view_name;

-- 更新视图
CREATE OR REPLACE VIEW view_name AS 
SELECT * FROM tablename;

9.2 存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 创建存储过程
DELIMITER //
CREATE PROCEDURE procedure_name(IN param1 INT, OUT param2 VARCHAR(100))
BEGIN
    SELECT * FROM tablename WHERE id = param1;
    SET param2 = 'Result';
END //
DELIMITER ;

-- 调用存储过程
CALL procedure_name(1, @result);
SELECT @result;

-- 删除存储过程
DROP PROCEDURE procedure_name;

9.3 触发器

1
2
3
4
5
6
7
8
9
10
11
12
-- 创建触发器
DELIMITER //
CREATE TRIGGER trigger_name
BEFORE INSERT ON tablename
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
END //
DELIMITER ;

-- 删除触发器
DROP TRIGGER trigger_name;

十、常见问题

10.1 启动问题

问题：启动 MySQL 时提示 “No directory, logging in with HOME=/“

1
2
3
sudo service mysql stop
sudo usermod -d /var/lib/mysql/ mysql
sudo service mysql start

问题：无法启动 MySQL 服务

1
2
3
4
5
6
7
8
# 查看错误日志
sudo tail -f /var/log/mysql/error.log

# 检查端口占用
sudo netstat -tlnp | grep 3306

# 检查 MySQL 进程
ps aux | grep mysql

10.2 字符集问题

1
2
3
4
5
6
7
8
-- 查看数据库字符集
SHOW VARIABLES LIKE 'character_set%';

-- 修改数据库字符集
ALTER DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 修改表字符集
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

10.3 忘记 root 密码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 1. 停止 MySQL 服务
sudo systemctl stop mysql

# 2. 以安全模式启动
sudo mysqld_safe --skip-grant-tables &

# 3. 无密码登录
mysql -u root

# 4. 重置密码
USE mysql;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
FLUSH PRIVILEGES;

# 5. 重启 MySQL 服务
sudo systemctl restart mysql

10.4 连接数过多

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 查看最大连接数
SHOW VARIABLES LIKE 'max_connections';

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';

-- 修改最大连接数
SET GLOBAL max_connections = 500;

-- 查看所有连接
SHOW PROCESSLIST;

-- 终止特定连接
KILL connection_id;

10.5 表损坏修复

1
2
3
4
5
6
7
8
9
10
-- 检查表
CHECK TABLE tablename;

-- 修复表
REPAIR TABLE tablename;

-- 如果修复失败，使用命令行工具
# myisamchk -r /var/lib/mysql/dbname/tablename.MYI
# 或
# innodb_force_recovery = 1  # 在 my.cnf 中设置

十一、实用技巧

11.1 批量操作

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 批量插入（使用 VALUES）
INSERT INTO tablename(column1, column2) VALUES
    (1, 'value1'),
    (2, 'value2'),
    (3, 'value3');

-- 批量更新（使用 CASE）
UPDATE tablename SET columnname = CASE id
    WHEN 1 THEN 'value1'
    WHEN 2 THEN 'value2'
    WHEN 3 THEN 'value3'
END
WHERE id IN (1, 2, 3);

11.2 数据导入导出

1
2
3
4
5
6
7
8
9
10
# 导出为 CSV
mysql -u username -p -e "SELECT * FROM dbname.tablename" > output.csv

# 导入 CSV
LOAD DATA INFILE '/path/to/file.csv'
INTO TABLE tablename
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

11.3 监控与统计

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 查看数据库大小
SELECT 
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
GROUP BY table_schema;

-- 查看表大小
SELECT 
    table_name AS 'Table',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = 'dbname'
ORDER BY (data_length + index_length) DESC;

-- 查看表行数
SELECT 
    table_name,
    table_rows
FROM information_schema.tables
WHERE table_schema = 'dbname';

十二、总结

本文整理了 MySQL 日常开发和管理中常用的命令和操作，包括：

  • ✅ 环境配置：安装、启动、配置
  • ✅ 数据库操作：创建、删除、修改数据库和表
  • ✅ 数据操作：增删改查、聚合、连接查询
  • ✅ 用户权限：用户管理、权限控制
  • ✅ 备份恢复：数据备份和恢复策略
  • ✅ 性能优化：查询优化、索引优化
  • ✅ 高级特性：事务、视图、存储过程、触发器
  • ✅ 实用技巧：常用函数、批量操作、监控统计

掌握这些常用命令和技巧，可以大大提高 MySQL 的使用效率。建议根据实际需求，深入学习相关领域的知识。

十三、相关参考

Prev
2017-12-20 13:31:00 #MySQL #Database #SQL
Next