MySQL 数据操作详解

1. 插入数据（INSERT）

基本语法

INSERT INTO TableName (Column1, Column2, Column3)
VALUES (value1, value2, value3);

示例

  1. 按列名插入一条记录：

    INSERT INTO TableName (Column1, Column2, Column3)
VALUES (value1, value2, value3);

  2. 按表字段顺序插入一条记录：

    INSERT INTO TableName
VALUES (value1, value2, value3);

  3. 插入多条记录：

    INSERT INTO TableName
VALUES (value1, value2, value3), (value4, value5, value6);

  4. 从其他表中插入数据：

    INSERT INTO table1 (field1, field2)
SELECT field1, field2 FROM table2;

  5. 插入所有字段：

    INSERT INTO World_Events SELECT * FROM National_Events;

性能优化

  • 批量插入时可使用 LOAD DATA INFILE 提高性能。
  • 如果表非空且有索引，可调整 bulk_insert_buffer_size 参数以提高插入速度。
  • 在执行批量插入前，可以暂时禁用键或加锁表。

2. 更新数据（UPDATE）

基本语法

UPDATE TableName 
SET field1 = newvalue1, field2 = newvalue2 
WHERE criteria 
ORDER BY field 
LIMIT n;

示例

  1. 更新单个字段：

    UPDATE antiques
SET price = 500.00
WHERE item = 'Chair';

  2. 使用子查询更新字段：

    UPDATE owner 
SET ownerfirstname = 'John'
WHERE ownerid = (SELECT buyerid FROM antiques WHERE item = 'Bookcase');

  3. 更新多个表：

    UPDATE tbl1 
JOIN tbl2 ON tbl1.ID = tbl2.ID
SET tbl1.col1 = tbl1.col1 + 1
WHERE tbl2.status = 'Active';

  4. 使用函数更新字段：

    UPDATE tbl 
SET names = REPLACE(names, 'aaa', 'zzz');

  5. 按顺序更新部分行：

    UPDATE posts
SET deleted = TRUE
ORDER BY date
LIMIT 1;

注意事项

  • ORDER BY 和 LIMIT 可用来指定更新的行。
  • 不允许在更新操作中直接查询同一表，例如： 
    UPDATE spip_auteurs 
SET pass = (SELECT pass FROM spip_auteurs WHERE login = 'paul') 
WHERE login = 'admin';
    上述语句会导致错误 ERROR 1093。

性能优化

  • 更新操作的速度与需要更新的索引数量有关。
  • 对于 MyISAM 动态格式表，如果更新导致行变大，可能会分块存储，影响读取性能，建议定期运行 OPTIMIZE TABLE。

3. 替换数据（REPLACE）

基本语法

REPLACE 与 INSERT 类似，但如果记录的主键或唯一索引重复，REPLACE 会先删除旧记录，再插入新记录。

示例

REPLACE INTO TableName (Column1, Column2, Column3)
VALUES (value1, value2, value3);

4. 删除数据（DELETE 和 TRUNCATE）

DELETE

  • 删除满足条件的记录：

    DELETE FROM TableName WHERE condition;

  • 删除指定数量的记录：

    DELETE FROM TableName 
WHERE item = 'Ottoman' 
ORDER BY id 
LIMIT 1;

TRUNCATE

  • 快速删除所有记录： 
    TRUNCATE TABLE TableName;

区别

特性 DELETE TRUNCATE
作用 删除指定记录 删除表中所有记录
速度 慢（逐行删除） 快（删除表并重建）
事务安全性 支持事务 不支持事务
是否计数 返回删除的行数 不返回删除的行数
外键影响 会触发外键约束 类似 DELETE，但更高效

性能优化

  • 对索引较多的表，增加缓存（如调整 key_buffer_size）可以提升 DELETE 速度。
  • 如果删除的记录较多（如超过 30%），建议运行 OPTIMIZE TABLE。

5. 忽略错误（IGNORE）

  • 使用 INSERT IGNORE 和 REPLACE IGNORE 遇到重复键错误时不会终止语句，而是跳过该错误并记录警告： 
    INSERT IGNORE INTO TableName (Column1, Column2)
VALUES (value1, value2);

参考资料

Last modified: Friday, 17 January 2025, 7:25 PM