当前位置： 移动技术网 > IT编程>数据库>Mysql > MySQL中UPDATE语句使用的实例教程

MySQL中UPDATE语句使用的实例教程

2017年12月12日  | 移动技术网IT编程  | 我要评论

一、update常见用法
首先建立测试环境：
 

drop table if exists t_test;
create table t_test (
 bs bigint(20) not null auto_increment,
 username varchar(20) not null,
 password varchar(20) default null,
 remark varchar(200) default null,
 primary key (bs)
) engine=innodb auto_increment=4 default charset=gbk;
 
 
insert into t_test values (1,'lavasoft','123456',null);
insert into t_test values (2,'hello',null,null);
insert into t_test values (3,'haha',zz,tt);

 
1、set一个字段
在表t_test中设置第二条记录（bs为2）的password为'***'。

update t_test t 
  set t.password = '***' 
 where t.bs = 2;

 
2、set多个字段
在表t_test中设置第一条记录（bs为1）的password为'*'、remark为'*'。

update t_test t 
  set t.password = '*', t.remark = '*' 
 where t.bs = 1;

 
3、set null值
在表t_test中设置第三条记录（bs为3）的password为null、remark为null。

update t_test t 
  set t.password = null, t.remark = null 
 where t.bs = 3;

 
这个是按照标准语法写的，在不同的数据库系统中，update还有更多的写法，但是标准写法都是支持的。以上三个例子为了说明情况，每次都更新一行。在实际中，可以通过where语句约束来控制更新行数。

二、update使用中的相关性能问题以及解决方法
update的功能是更新表中的数据。这的语法和insert的第二种用法相似。必须提供表名以及set表达式，在后面可以加where以限制更新的记录范围。

update table_anem set column_name1 = value1, column_name2 = value2,  where ;

如下面的语句将users表中id等于123的记录的age改为24

update users set age = 24 where id = 123;

同样，可以使用update更新多个字段的值

update users set age = 24, name = 'mike' where id = 123;

上面的update语句通过where指定一个条件，否则，update将更新表中的所有记录的值
百万级别的数据，对于mysql应该没有问题。

你这个sql的问题是，相当于修改one表里面所有记录的age信息，而修改的过程是，对于每一条one里面的记录，去two里面查询，再修改。而且，期间很可能会有锁之类的东西。
首先，这种sql不应该出现在业务逻辑里面，而应该是后台的job里面。
如果一定要这么做，可以试着用相反的方式，如果不一样的记录不是特别多，那就找到one表里面age记录跟two表不一样的记录，再修改, 例如大概象下面（可能语法不太对）：

update one，two 
set one.age=two.age 
where one.id=two.id and one.age != two.age

当我把数据调到了1000w就更新不了了，下面我来分析原因。
实例：需要根据用户日志的ip地址计算出其地理地址
表结构：
用户日志表（200万条记录），其中address是待填充的字段：

create table `tmp_open_ip` (
 `email` varchar(60) not null default '',
 `address` varchar(50) not null default '',
 `ip` int(10) unsigned not null default '0',
 key `email` (`email`),
 key `ip` (`ip`)
) engine=myisam default charset=utf8

另ip地址数据库表（44万条记录）

create table `ip` (
`s` int(10) unsigned not null default '0' comment '开始ip',
`e` int(10) unsigned not null default '0' comment '结束ip',
`a` varchar(50) not null default '',
key `s` (`s`),
key `e` (`e`)
) engine=myisam default charset=utf8

需要根据用户日志表 tmp_open_ip 里的 ip字段到ip地址数据库表里查询出对应的地理地址，将地址填充到address字段。
使用如下update语句执行：

update tmp_open_ip as u 
inner join ip 
on u.ip between ip.s and ip.e 
set u.address = ip.a

在笔者的电脑上运行了速度非常之慢，执行了一个多小时（4500s）都没有完，也不知道还要多久。
实在看不过去，于是想到使用insert 是否会快一些，于是重新导一张表 tmp_open_log 与tmp_open_log完全一致。
创建一张表 tmp_open_address，是insert的目标表，为了速度更快，没建索引：

create table `tmp_open_address` (
`email` varchar(60) not null default '',
`address` varchar(50) not null default '',
`ip` int(10) unsigned not null default '0'
) engine=myisam default charset=utf8

执行insert 语句

insert into tmp_open_address (email,address,ip)
select l.email,ip.a,l.ip
 from tmp_open_log as l inner join ip on l.ip between ip.s and ip.e ;
/* affected rows: 2,543,124 found rows: 0 warnings: 0 duration for 3 queries: 16.922 sec. */

不到17s！本来还想去倒杯水、稍事休息一下，结果已经执行完毕。

到本文写完时，前面的update语句已经执行了5000s，结束仍是遥遥无期。
所以，对于大数据量执行update时，可以考虑改用insert 语句实现，可能麻烦一些，但高速带来的收益远大于麻烦！
后记：
直接杀死了update进程，去看看update执行了多少：运行

select * from `tmp_open_ip` where address!=''

结果只有 11,373 ，照这个速度，要运行n天....

您可能感兴趣的文章:

如对本文有疑问， 点击进行留言回复！！

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网