数据更新

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