MySQL中UPDATE语句使用的实例教程
作者:goldensun 发布时间:2024-01-27 00:10:40
一、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天....
猜你喜欢
- 前言以下述环境为例:python=3.7pytorch=1.7.1torchvision==0.8.2cudatoolkit=10.1pyt
- 本文实例讲述了Python实现随机创建电话号码的方法。分享给大家供大家参考,具体如下:当需要随机的生成一些电话号码的时候,可以使用以下脚本,
- Python里的多线程是假的多线程,不管有多少核,同一时间只能在一个核中进行操作!利用Python的多线程,只是利用CPU上下文切换的优势,
- 需求是这样的,我在.net程序里操作数据时将一些字段数据加密了,这些数据是很多系统共用的,其中一delphi程序也需要用到,并且需要将数据解
- 1、MSSQL2000 SELECT 表名 = case when a.colorder=1 then d.name else '&
- 实例源码:#pip3 install opencv-pythonimport cv2from datetime import datetim
- 关于数据库性能的故事面试时多多少少会讲到数据库上的事情,“你对数据库的掌握如何?”,什么时候最考验数据库的性能,答应主要方面上讲就是大数据量
- 楔子在 TensorFlow 中,可以给一个 tensor 增加一个维度、删除一个维度,那么在 Numpy 中该怎么呢?删除维度、增加维度先
- 最近一直在“深山老林”中修炼“支付宝新版收银台”,经历了白板设计,视觉设计,前端开发,前后端联调各个阶段。点点滴滴……重点谈谈对交互设计的感
- RSS(Really Simple Syndication)是一种描述和同步网站内容的格式,是使用最广泛的XML应用。RSS搭建了信息迅速传
- 前面一篇文章有说过,利用scrapy来爬取图片,是为了对图片数据进行分类而收集数据。本篇文章就是利用上次爬取的图片数据,根据图片的颜色特征来
- 1. 简述我们在用scrapy爬取数据时,首先就要明确我们要爬取什么数据。scrapy提供了Item对象这种简单的容器,我们可以通过Item
- SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作,方便自己写SQL时方便一点,想贴上来,一起看看,同时希望
- 前几天,使用python时遇到这么一个需求,删除一个列表中值为1的元素。我寻思着使用remove方法,但是remove方法只会删除第一个,于
- ThinkPHP的分组功能是广为开发者使用的一个具有很大实用价值的功能,该功能可以解决中大型项目情况中MVC分层文件过多导致不易管理的问题。
- 假设有如下目录结构:-- dir0| file1.py| file2.py| dir3| file3.py| dir4| file4.pyd
- 最近在开发一个web应用中需要用到带搜索功能下拉框,曾经尝试网上的django 包, django-select2-forms, 这是款功能
- Notepad++ 是一款非常有特色的编辑器,软件小巧高效,支持27种编程语言,通吃C,C++ ,Java ,C#, XML, HTML,
- 我们的搜索示例仍然相当地简单,特别从数据验证方面来讲;我们仅仅只验证搜索关键值是否为空。 然后许多HTML表单包含着比检测值是否为空更为复杂
- MySQL超长字符截断又名"SQL-Column-Truncation",是安全研究者Stefan Esser在2008