MySQL 同表复制数据

后端新人学习SQL,今天学会了库内复制数据。

我觉得再这么下去,我真敢说我写过PHP了……

需求很简单,在同一个表中复制数据。以前的代码是在PHP里先 select *,然后 extract 成变量,再组合成一个大 sql,最后插入。我觉得这样不好,首先要执行两次 sql,其次写那么一大篇 sql 也挺麻烦的。于是研究了下,发现并不复杂,这里总结一下:

如果是从别的表里导入数据,可以这样写:

INSERT INTO `table`
SELECT *
FROM `table2`
WHERE `id`=1

如果是同表,并且表里没有主键,这样也好使;但是有主键的话,会被告知主键重复,这个时候就只能把字段都写出来了:

# 假设表结构为 id, col1, col2, col3
INSERT INTO `table`
SELECT NULL, col1, col2, col3
FROM `table`
WHERE `id`=1

这里字段的顺序很重要,要参照表的顺序来写(我是用 MySQL Wordbench 连上库,然后用Alert table 看的);不过好处在于,如果我们需要更改其中某个字段的值,只要在 sql 里直接写就好。比如我们复制后想交换后两个字段,或者改变某个字段的值,就可以这样:

# 假设表结构为 id, name, age, sex
# 交换age和sex
INSERT INTO `table`
SELECT NULL, `name`, `sex`, `age`
FROM `table`
WHERE `id`=1

# 更改某字段的值:将复制得到的name加上copy_前缀
INSERT INTO `table`
SELECT NULL, CONCAT('copy_', `name`), `age`, `sex`
FROM `table`
WHERE `id`=1

id 这种自增的主键,直接插入 NULL 就可以了,MySQL 会自动帮我们补全(如前几段所示)。

同时插入多条数据也很简单,这样即可:

# 假设表结构为id, name, age, sex
# 复制 id<10 的字段,加上copy_标识
INSERT INTO `table`
SELECT NULL, CONCAT('copy_', `name`), `age`, `sex`
FROM `table`
WHERE `id`<10

实话实说我不太懂MySQL,不过这样写我觉得有几个好处:

  1. 省事儿,可以不再把变量一一抄上
  2. 可以一次复制多条数据
  3. 只执行一行sql,速度应该会更快

PHP通过Sql生成带特定索引的数组

虽不明,但觉厉。

不明白原理,从别人代码中看到的,蛮好用,不过似乎性能不好:

SELECT id, sum(rmb) AS rmb, sum(device) AS device
FROM log_table
WHERE date>'2012-10-20'
GROUP BY id

这样一段Sql,用PHP自带的pdo这样请求:

$result = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC|PDO::FETCH_UNIQUE|PDO::FETCH_GROUP);

可以得到一个数组,使用sql中第一个字段也就是id为索引,每个元素为一个含两个属性(rmb, device)的对象。换成json写法大概是这样:

// 以前
[{id: 1, rmb: 1, device: 1}, {id: 2, rmb: 2, device:2}]

// 现在
{'1': {rmb: 1, device: 1}, '2': {rmb: 2, device: 2}}

这样再操作会方便很多。