2016年5月

mysql批量更新多条记录的同一字段为不同值

mysql更新数据的某个字段,一般这样写:

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

也可以这样用in指定要更新的记录:

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

这里注意 ‘other_values’ 是一个逗号(,)分隔的字符串,如:1,2,3

如果更新多条数据而且每条记录要更新的值不同,可能很多人会这样写:

foreach ($values as $id => $myvalue) {
    $sql = "UPDATE mytable SET myfield = $myvalue WHERE id = $id";
    mysql_query($sql);
}

即是循环一条一条的更新记录。一条记录update一次,这样性能很差,也很容易造成阻塞。

那么能不能一条sql语句实现批量更新呢?mysql并没有提供直接的方法来实现批量更新,但是可以用点小技巧来实现。

UPDATE mytable
    SET myfield = CASE id
        WHEN 1 THEN 'myvalue1'
        WHEN 2 THEN 'myvalue2'
        WHEN 3 THEN 'myvalue3'
    END
WHERE other_field ('other_values')

如果where条件查询出记录的id不在CASE范围内,myfield将被设置为空。

如果更新多个值的话,只需要稍加修改:

UPDATE mytable
    SET myfield1 = CASE id
        WHEN 1 THEN 'myvalue11'
        WHEN 2 THEN 'myvalue12'
        WHEN 3 THEN 'myvalue13'
    END,
    myfield2 = CASE id
        WHEN 1 THEN 'myvalue21'
        WHEN 2 THEN 'myvalue22'
        WHEN 3 THEN 'myvalue23'
    END
WHERE id IN (1,2,3)

这里以php为例,构造这两条mysql语句:

  • 更新多条单个字段为不同值, mysql模式
$ids_values = array(
    1 => 11,
    2 => 22,
    3 => 33,
    4 => 44,
    5 => 55,
    6 => 66,
    7 => 77,
    8 => 88,
);
 
$ids = implode(',', array_keys($ids_values ));
$sql = "UPDATE mytable SET myfield = CASE id ";
foreach ($ids_values as $id=> $myvalue) {
    $sql .= sprintf("WHEN %d THEN %d ", $id, $myvalue);
}
$sql .= "END WHERE id IN ($ids)";
echo $sql.";<br/>";

输出

UPDATE mytable SET myfield = CASE id WHEN 1 THEN 11 WHEN 2 THEN 22 WHEN 3 THEN 33 WHEN 4 THEN 44 WHEN 5 THEN 55 WHEN 6 THEN 66 WHEN 7 THEN 77 WHEN 8 THEN 88 END WHERE id IN (1,2,3,4,5,6,7,8);

  • 更新多个字段为不同值, PDO模式
$data = array(array('id' => 1, 'myfield1val' => 11, 'myfield2val' => 111), array('id' => 2, 'myfield1val' => 22, 'myfield2val' => 222));
$where_in_ids = implode(',', array_map(function($v) {return ":id_" . $v['id'];}, $data));
$update_sql = 'UPDATE mytable SET';
$params = array();

$update_sql .= ' myfield1 = CASE id';
foreach($data as $key => $item) {
    $update_sql .= " WHEN :id_" . $key . " THEN :myfield1val_" . $key . " ";
    $params[":id_" . $key] = $item['id'];
    $params[":myfield1val_" . $key] = $item['myfield1val'];
}
$update_sql .= " END";

$update_sql .= ',myfield2 = CASE id';
foreach($data as $key => $item) {
    $update_sql .= " WHEN :id_" . $key . " THEN :myfield2val_" . $key . " ";
    $params[":id_" . $key] = $item['id'];
    $params[":myfield1va2_" . $key] = $item['myfield2val'];
}
$update_sql .= " END";

$update_sql .= " WHERE id IN (" . $where_in_ids . ")";
echo $update_sql.";<br/>";
var_dump($params);

输出

UPDATE mytable SET myfield1 = CASE id WHEN :id_0 THEN :myfield1val_0 WHEN :id_1 THEN :myfield1val_1 END,myfield2 = CASE id WHEN :id_0 THEN :myfield2val_0 WHEN :id_1 THEN :myfield2val_1 END WHERE id IN (:id_1,:id_2);

array (size=6)
 ':id_0' => int 1
 ':myfield1val_0' => int 11
 ':id_1' => int 2
 ':myfield1val_1' => int 22
 ':myfield1va2_0' => int 111
 ':myfield1va2_1' => int 222

另外三种批量更新方式

1. replace into 批量更新

replace into mytable(id, myfield) values (1,'value1'),(2,'value2'),(3,'value3');

2. insert into ...on duplicate key update批量更新

insert into mytable(id, myfield1, myfield2) values (1,'value11','value21'),(2,'value12','value22'),(3,'value13','value23') on duplicate key update myfield1=values(myfield2),values(myfield2)+values(id);

3. 临时表

DROP TABLE IF EXISTS `tmptable`;
create temporary table tmptable(id int(4) primary key,myfield varchar(50));
insert into tmptable values (1,'value1'),(2,'value2'),(3,'value3');
update mytable, tmptable set mytable.myfield = tmptable.myfield where mytable.id = tmptable.id;
  • 【replace into】和【insert into】更新都依赖于主键或唯一值,并都可能造成新增记录的操作的结构隐患
  • 【replace into】操作本质是对重复记录先delete然后insert,如果更新的字段不全缺失的字段将被设置成缺省值
  • 【insert into】则只是update重复的记录,更改的字段只能依循公式值
  • 【临时表】方式需要用户有temporary 表的create 权限
  • 数量较少时【replace into】和【insert into】性能最好,数量大时【临时表】最好,【CASE】则具有通用型也不具结构隐患

SSL/TLS协议和https访问流程

可以先阅读《密码学笔记》了解对称算法和不对称算法,以及《数字签名是什么?》了解数字签名如何保证安全。

http:超文本传输协议;

https:安全套接字超文本传输协议;为了数据的安全传输,HTTPS在HTTP的基础上添加SSL/TLS协议;SSL/TLS依靠证书来验证服务器身份;并为浏览器和服务器之间通信加密。

HTTPS其实是有两部分组成:HTTP + SSL/TLS,也就是在HTTP上又加了一层处理加密信息的模块。服务端和客户端的信息传输都会通过TLS进行加密,所以传输的数据都是加密后的数据。

用途两种:一种是建立一个信息通道,来保证数据传输的安全;另一种就是确认网站的真实性,凡是使用了 https 的网站,都可以通过点击浏览器地址栏的锁头标志来查看网站认证之后的真实信息,也可以通过 CA 机构颁发的安全签章来查询。

http和https的区别:

  1. https协议需要到CA(Certificate Authority)申请证书,一般证书很少免费,需要交费;
  2. http是超文本传输协议,信息是明文传输;https则是具有安全性的SSL加密传输协议;
  3. http和https使用的是完全不同的连接方式;http使用80端口;https使用443端口;

采用https的服务器必须从CA (Certificate Authority)申请一个用于证明服务器用途类型的证书。该证书只有用于对应的服务器的时候,客户端才信任此主机。

https访问流程是如何确认安全的:

  1. 访问XXX,客户端(浏览器)连接xxx:443并发送带有Cipher的ClientHellod内容
  2. xxx:443收到ClientHellod后与自己支持的SSL/TLS版本协议对比,返回双方都支持的Cipher(接下去就通讯都依靠Cipher规定的算法),同时发送xxx数字证书(包含xxx域名,颁发证书的CA,过期时间,XXX不对称密钥的公钥,经过CA私钥加密证书信息摘要后的【签名】等信息)
  3. 客户端验证证书,生成XXX数字证书的信息摘要并和CA不对称密钥的公钥解密签名后的摘要进行比较确认证书有效,并从此确认了xxx提供的xxx不对称密钥的公钥是有效的
  4. 至此双方通讯安全可以得到保证,客户端发送内容时带上内容摘要的加密签名,服务端获取内容并生成摘要与解密签名后的摘要进行比较从而确认安全
  5. 但不对称算法相当慢所以之后通讯采用对称加密算法,这里就需要先交换一个对称加密的密钥,一般使用Cipher密钥交换协议进行对称密钥交换,客户端生成一个随机值作为对称加密密钥,使用xxx不对称密钥的公钥加密后和对称加密密钥的Cipher算出的MAC值一起发送给xxx:443,xxx:443用不对称密钥的私钥解密后得到对称加密的密钥(会生成对称加密密钥的MAC做验证)
  6. 之后通讯都是一方对内容的摘要进行签名,然后另一方解密签名对比摘要

数字证书:数字证书是一种权威性的电子文档,由权威公正的第三方机构,即CA中心签发的证书。它以数字证书为核心的加密技术可以对网络上传输的信息进行加密和解密、数字签名和签名验证,确保网上传递信息的机密性、完整性。 使用了数字证书,即使您发送的信息在网上被他人截获,甚至您丢失了个人的账户、密码等信息,仍可以保证您的账户、资金安全。 VeriSign、GeoTrust、Thawte 是国际权威数字证书颁发认证机构的“三巨头”,其中,应用最广的为VerSign签发的电子商务数字证书。数字证书的持有人都有一对公钥和私钥,公钥是公开的,而私钥是由证书持有人在自己本地生成并持有,并且必须妥善保管和注意保密。

CA:证书颁发机构,CA中心自己握有CA私钥,并存储CA公钥到客户端(权威的CA一般已经安装到你的浏览器客户端),xxx需要拿自己的xxx公钥去CA申请xxx的数字证书存在服务端,并在在客户端请求时提供。

Cipher:每种Cipher规定了一系列算法,主要就是SSL/TLS版本差异,包含了四部分信息,分别是

  • 密钥交换算法(多为不对称加密算法),用于决定客户端与服务器之间在握手的过程中如何认证,用到的算法包括RSA,Diffie-Hellman,ECDH,PSK等
  • 对称加密算法,用于加密消息流,该名称后通常会带有两个数字,分别表示密钥的长度和初始向量的长度,比如DES 56/56, RC2 56/128, RC4 128/128, AES 128/128, AES 256/256
  • 报文认证信息码(MAC)算法,一般为hash算法,用于创建报文摘要,确保消息的完整性(没有被篡改),算法包括MD5,SHA等。
  • PRF(伪随机数函数),用于生成“master secret”。

比如 TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA,从其名字可知,它是

  • 基于TLS协议的;
  • 使用ECDHE、RSA作为密钥交换算法;
  • 加密算法是AES(密钥和初始向量的长度都是256);
  • MAC算法(这里就是哈希算法)是SHA。

SSH的SSL/TLS流程应用:

ssl的签名流程同时也用在了SSH协议,相关内容可以阅读《SSH原理与运用(一):远程登录》,但SSH并没有CA中心对公钥进行验证,所以服务器一般会公布自己公钥的指纹。

本文多为自己阅读相关文章和理解后的内容,并未实践抓取通讯流程,难免有不妥之处。

附:
HTTPS 理论详解与实践
Nginx基本配置备忘
HTTPS 简介及使用官方工具 Certbot 配置 Let’s Encrypt SSL 安全证书详细教程
手把手教你在Nginx上使用CertBot
HTTPS那些事(一)HTTPS原理