数据更新

UPDATE table_name SET columnname1 = value1 [, columname2 = value2]...
WHERE search_condition

批量更新

UPDATE mytable SET myfield = 'value' WHERE other_field = 'other_value';

如果更新同一字段为同一个值，mysql 也很简单，修改下 where 即可：

UPDATE mytable SET myfield = 'value' WHERE other_field in ('other_values');

这里注意 ‘other_values’ 是一个逗号(，)分隔的字符串，如：1,2,3，那如果更新多条数据为不同的值，可能很多人会这样写：

foreach ($display_order as $id => $ordinal) {
    $sql = "UPDATE categories SET display_order = $ordinal WHERE id = $id";
    mysql_query($sql);
}

即是循环一条一条的更新记录。一条记录 update 一次，这样性能很差，也很容易造成阻塞。那么能不能一条 sql 语句实现批量更新呢？mysql 并没有提供直接的方法来实现批量更新，但是可以用点小技巧来实现。

UPDATE mytable
    SET myfield = CASE id
        WHEN 1 THEN 'value'
        WHEN 2 THEN 'value'
        WHEN 3 THEN 'value'
    END
WHERE id IN (1,2,3)

这里使用了 case when 这个小技巧来实现批量更新。举个例子：

UPDATE categories
    SET display_order = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END
WHERE id IN (1,2,3)

这句 sql 的意思是，更新 display_order 字段，如果 id=1 则 display_order 的值为 3，如果 id=2 则 display_order 的值为 4，如果 id=3 则 display_order 的值为 5。即是将条件语句写在了一起。这里的 where 部分不影响代码的执行，但是会提高 sql 执行的效率。确保 sql 语句仅执行需要修改的行数，这里只有 3 条数据进行更新，而 where 子句确保只有 3 行数据执行。如果更新多个值的话，只需要稍加修改：

UPDATE categories
    SET display_order = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END,
    title = CASE id
        WHEN 1 THEN 'New Title 1'
        WHEN 2 THEN 'New Title 2'
        WHEN 3 THEN 'New Title 3'
    END
WHERE id IN (1,2,3)

到这里，已经完成一条 mysql 语句更新多条记录了。但是要在业务中运用，需要结合服务端语言，这里以 php 为例，构造这条 mysql 语句：

$display_order = array(
    1 => 4,
    2 => 1,
    3 => 2,
    4 => 3,
    5 => 9,
    6 => 5,
    7 => 8,
    8 => 9
);

$ids = implode(',', array_keys($display_order));
$sql = "UPDATE categories SET display_order = CASE id ";
foreach ($display_order as $id => $ordinal) {
    $sql .= sprintf("WHEN %d THEN %d ", $id, $ordinal);
}
$sql .= "END WHERE id IN ($ids)";
echo $sql;

子查询更新

注意，有时候如果用子查询然后再进行 Update 操作的话，譬如：

update wms_cabinet_form set cabf_enabled=0
where cabf_id in (
    SELECT wms_cabinet_form.cabf_id FROM wms_cabinet_form
    where wms_cabinet_form.cabf_enabled=1
)

等于不能先 select 出同一表中的某些值，再 update 这个表(在同一语句中)。这样的话，我们就需要创建一个子查询或者临时表：

update wms_cabinet_form set cabf_enabled=0 where cabf_id in (
    SELECT a.cabf_id FROM (select tmp.* from wms_cabinet_form tmp) a
    where a.cabf_enabled=1
)

存在性更像

Mysql 处理某个唯一索引时存在则更新，不存在则插入的情况应该是很常见的，网上也有很多类似的文章，我今天就讲讲当这个唯一的索引是多列唯一索引时可能会遇到的问题和方法。

方法一：

使用

INSERT INTO ON … DUPLICATE KEY UPDATE …

：

表的创建如下：

CREATE TABLE `test_table` (
  `id`  int(11) NOT NULL AUTO_INCREMENT ,
  `var1`  varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `var2`  tinyint(1) NOT NULL DEFAULT '0',
  `var3`  varchar(100) character set utf8 default NULL,
  `value1`  int(11) NOT NULL DEFAULT '1',
  `value2`  int(11) NULL DEFAULT NULL,
  `value3`  int(5) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE INDEX `index_var` (`var1`, `var2`, `var3`)
) ENGINE=MyISAM DEFAULT CHARACTER SET=latin1 AUTO_INCREMENT=1;

其中该表中 var1、var2 和 var3 完全相同的记录只能有一条，所以建了一个多列唯一索引 index_var，这样一来我们就可以使用 INSERT INTO ON … DUPLICATE KEY UPDATE … 来实现插入数据时存在则更新，不存在则插入的功能了，如下：

INSERT INTO `test_table`
(`var1`, `var2`, `var3`, `value1`, `value2`, `value3`) VALUES
('abcd', 0, 'xyz', 1, 2, 3)
ON DUPLICATE KEY UPDATE `value1` = `value1` + 1 AND
`value2` = `value2` + 2 AND `value3` = `value3` + 3;

该条插入语句的含义是：向 test_table 表中插入，如果不存在 val1 = ‘abcd’，val2 = 0, val3 = ‘xyz’的记录，那就插入

val1 = ‘abcd’，val2 = 0, val3 = ‘xyz’，value1 = 1, value2 = 2, value3 = 3 的记录，

如果存在，那就更新 value1 的值为 value1+1，更新 value2 的值为 value2+2，更新 value3 的值为 value3+3。

这样，的确是没有问题的，但是，如果表的创建如下：

CREATE TABLE `test_table` (
  `id`  int(11) NOT NULL AUTO_INCREMENT ,
  `var1`  varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
  `var2`  tinyint(1) NOT NULL DEFAULT '0',
  `var3`  varchar(1024) character set utf8 default NULL,
  `value1`  int(11) NOT NULL DEFAULT '1',
  `value2`  int(11) NULL DEFAULT NULL,
  `value3`  int(5) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE INDEX `index_var` (`var1`, `var2`, `var3`)
) ENGINE=MyISAM DEFAULT CHARACTER SET=latin1 AUTO_INCREMENT=1;

注意：var1 和 var3 的最大长度由 100 变成了 1024，此时执行该创建语句时会报如下错误：

Specified key was too long; max key length is 1000 bytes

这是由于 index*var 索引的为 1024 * 3 + 1 + 1024 _ 3 > 1000 导致的，如果遇到这种情况怎么办？有两种解决办法。

第一，将数据库的 engine 由 MyISAM 换成 InnoDB 就可以了，那么这两个引擎有什么区别呢？

看这里

不过，这样换有一个缺点，就是 InnoDB 的性能没有 MyISAM 的好，那么如果想要不牺牲性能的话，那就只有用第二个方法了，也就是我们这里说的方法二！

方法二：

使用 dual 虚拟表来实现。

使用 dual 虚拟表来实现的话就不需要创建多列唯一索引了，表的创建如下：

CREATE TABLE `test_table` (
  `id`  int(11) NOT NULL AUTO_INCREMENT ,
  `var1`  varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
  `var2`  tinyint(1) NOT NULL DEFAULT '0',
  `var3`  varchar(1024) character set utf8 default NULL,
  `value1`  int(11) NOT NULL DEFAULT '1',
  `value2`  int(11) NULL DEFAULT NULL,
  `value3`  int(5) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM DEFAULT CHARACTER SET=latin1 AUTO_INCREMENT=1;

插入语句则是形如：

INSERT INTO table
(primarykey, field1, field2, ...)
SELECT key, value1, value2, ...
FROM dual
WHERE not exists (select * from table where primarykey = id);

的语句，此时我们可以用以下语句代替：

INSERT INTO `test_table` SELECT 0, 'abcd', 0, 'xyz', 1, 2, 3
FROM dual WHERE NOT EXISTS (
SELECT * FROM `test_table` WHERE
`var1` = 'abcd' AND `var2` = 0 AND `var3` = 'xyz');

最近更新于 0001-01-01