数据管理全景图
MySQL 数据管理功能概述
核心功能
- 数据导入导出：批量数据迁移与备份
- 记录管理：增删改查操作
- 条件匹配：精准筛选数据
- 数据转换：格式转换与清洗
应用场景
- 数据迁移：系统升级、服务器迁移
- 报表生成：定期导出分析数据
- 数据清洗：处理不规范数据
- 批量操作：高效管理大量记录
数据导入导出策略对比
| 方案 | mysqldump | LOAD DATA | SELECT INTO OUTFILE | 最佳适用场景 |
|---|---|---|---|---|
| 导出格式 | SQL | CSV/TSV | CSV/TSV | SQL：完整备份 CSV：数据分析 |
| 导入速度 | 慢 | 极快 | 不适用 | 大数据量：LOAD DATA |
| 导出速度 | 中等 | 不适用 | 快 | 部分导出：SELECT INTO |
| 数据结构 | 包含表结构 | 仅数据 | 仅数据 | 全库迁移：mysqldump |
| 压缩支持 | 支持 | 不支持 | 不支持 | 网络传输：压缩SQL |
| 跨平台性 | 优 | 良 | 良 | 异构系统：CSV |
| 事务支持 | 支持 | 可选 | 支持 | 事务数据：mysqldump |
一、数据导入导出
1. 导出数据
# 导出整个数据库
[root@localhost ~]# mysqldump -u root -p company > company_backup.sql
Enter password: ******
# 导出单表
[root@localhost ~]# mysqldump -u root -p company employees > employees.sql
# 导出CSV格式
[root@localhost ~]# mysql -u root -p -e "SELECT * FROM employees" company | sed 's/\t/,/g' > employees.csv
2. 导入数据
# 导入SQL文件
[root@localhost ~]# mysql -u root -p company < employees.sql
# 导入CSV文件
mysql> LOAD DATA INFILE '/var/lib/mysql-files/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
3. 跨服务器传输
# 直接迁移数据库
[root@localhost ~]# mysqldump -u root -p -h source_host company | mysql -u root -p -h dest_host company
二、管理表记录
1. 插入记录
# 单条插入
mysql> INSERT INTO employees (first_name, last_name, email)
VALUES ('John', 'Doe', 'john@example.com');
# 批量插入
mysql> INSERT INTO employees (first_name, last_name, email) VALUES
('Jane', 'Smith', 'jane@example.com'),
('Bob', 'Johnson', 'bob@example.com'),
('Alice', 'Williams', 'alice@example.com');
2. 更新记录
# 单条件更新
mysql> UPDATE employees SET salary = 5000 WHERE id = 1;
# 多条件更新
mysql> UPDATE employees SET salary = salary * 1.1
WHERE department = 'Sales' AND hire_date < '2023-01-01';
3. 删除记录
# 删除单条记录
mysql> DELETE FROM employees WHERE id = 100;
# 批量删除
mysql> DELETE FROM employees WHERE department = 'Temp' AND hire_date < '2022-01-01';
4. 查询记录
# 基础查询
mysql> SELECT * FROM employees;
# 指定列查询
mysql> SELECT first_name, last_name, salary FROM employees;
三、匹配条件演示
1. 基本条件匹配
# 等值匹配
mysql> SELECT * FROM employees WHERE department = 'IT';
# 范围匹配
mysql> SELECT * FROM employees WHERE salary BETWEEN 4000 AND 6000;
# 列表匹配
mysql> SELECT * FROM employees WHERE department IN ('HR', 'Finance');
2. 模糊匹配
# LIKE通配符
mysql> SELECT * FROM employees WHERE last_name LIKE 'Sm%';
# 正则表达式
mysql> SELECT * FROM employees WHERE email REGEXP '@example\.com$';
3. 空值处理
# 查找空值
mysql> SELECT * FROM employees WHERE phone IS NULL;
# 查找非空值
mysql> SELECT * FROM employees WHERE phone IS NOT NULL;
4. 复杂条件组合
# AND/OR组合
mysql> SELECT * FROM employees
WHERE (department = 'IT' AND salary > 5000)
OR (department = 'Sales' AND salary > 4000);
# 子查询匹配
mysql> SELECT * FROM employees
WHERE department IN (SELECT name FROM departments WHERE budget > 100000);
命令总结表格
| 演示命令 | 功能描述 | 关键参数 |
|---|---|---|
mysqldump -u user db > backup.sql |
数据库导出 | 数据库名 |
mysql -u user db < backup.sql |
数据库导入 | SQL文件 |
SELECT ... INTO OUTFILE 'file.csv' |
导出CSV | 文件路径 |
LOAD DATA INFILE 'file.csv' INTO TABLE |
导入CSV | 字段分隔符 |
INSERT INTO t VALUES (...) |
插入记录 | 列值 |
UPDATE t SET col=val WHERE ... |
更新记录 | 条件 |
DELETE FROM t WHERE ... |
删除记录 | 条件 |
SELECT * FROM t WHERE col LIKE 'pat%' |
模糊匹配 | LIKE/REGEXP |
SELECT * FROM t WHERE col IS NULL |
空值匹配 | IS NULL |
SELECT * FROM t WHERE col IN (1,2,3) |
列表匹配 | IN |
功能作用详解
1. 数据导入导出技术
导出格式对比
| 格式 | 命令示例 | 优点 | 缺点 |
|---|---|---|---|
| SQL | mysqldump -u user db |
完整结构+数据 | 文件较大 |
| CSV | SELECT ... INTO OUTFILE |
通用格式 | 无表结构 |
| XML | mysqldump --xml |
结构化 | 体积大 |
| JSON | SELECT JSON_OBJECT(...) |
易解析 | 需手动构造 |
导入优化技巧
-- 禁用索引加速导入
ALTER TABLE employees DISABLE KEYS;
LOAD DATA INFILE 'data.csv' INTO TABLE employees;
ALTER TABLE employees ENABLE KEYS;
-- 批量提交
SET autocommit=0;
LOAD DATA INFILE 'large.csv' INTO TABLE employees;
COMMIT;
2. 记录管理最佳实践
批量插入优化
-- 小批量多次插入
INSERT INTO t VALUES (...), (...), (...); -- 每次100-1000条
-- 使用LOAD DATA替代INSERT
LOAD DATA INFILE 'data.csv' INTO TABLE t
安全删除策略
-- 先查询再删除
SELECT * FROM employees WHERE department = 'Temp';
DELETE FROM employees WHERE department = 'Temp';
-- 使用LIMIT防止误删
DELETE FROM employees WHERE status = 'inactive' LIMIT 1000;
3. 高级匹配技术
全文搜索
-- 创建全文索引
ALTER TABLE articles ADD FULLTEXT INDEX ft_idx (title, content);
-- 自然语言搜索
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('database optimization' IN NATURAL LANGUAGE MODE);
-- 布尔搜索
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);
JSON数据匹配
-- 查询JSON字段
SELECT * FROM users
WHERE JSON_EXTRACT(profile, '$.skills') LIKE '%MySQL%';
-- JSON路径查询
SELECT * FROM users
WHERE JSON_CONTAINS(profile, '"MySQL"', '$.skills');
4. 性能优化技巧
索引优化匹配
-- 避免索引失效
SELECT * FROM employees WHERE YEAR(hire_date) = 2023; -- 索引失效
SELECT * FROM employees WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31'; -- 使用索引
-- 覆盖索引
SELECT id, name FROM employees WHERE department = 'IT'; -- 使用索引覆盖
分区裁剪
-- 创建分区表
CREATE TABLE logs (
id INT,
log_date DATE
) PARTITION BY RANGE (YEAR(log_date)) (...);
-- 分区查询优化
SELECT * FROM logs WHERE log_date BETWEEN '2023-01-01' AND '2023-12-31'; -- 只扫描2023分区
5. 数据清洗转换
数据格式化
-- 日期格式化
UPDATE employees SET hire_date = STR_TO_DATE('2023-01-15', '%Y-%m-%d')
WHERE id = 100;
-- 字符串处理
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
重复数据处理
-- 查找重复记录
SELECT email, COUNT(*) FROM employees GROUP BY email HAVING COUNT(*) > 1;
-- 删除重复记录
DELETE t1 FROM employees t1
INNER JOIN employees t2
WHERE t1.id < t2.id AND t1.email = t2.email;