MySQL表数据的DML操作

时间:2021-07-10 18:47:56

从过往MySQL数据库生产环境的维护工作中,总结的一些小经验和知识,未必有多深奥,但是对我们消除隐患,确保MySQL数据库生产环境四个9的作用非常有效之一的手段,运维人员要非常注意细节,尽量减低故障发生的概率。

.UPDATE语句报错(用法问题):
[SQL]  UPDATE tb_stuinfo SET stuName='王维' AND stuSex='男'AND stuHomeAddr='山西祁县'WHERE id=1;

[Err] 1292 - Truncated incorrect DOUBLE value: '王维'
百度发现1292错误多为:
   (1)MySQL 支持带符号和无符号的64比特值的运算。若你正在使用数字操作符 (如 +) 而其中一个操作数为无符号整数,则结果为无符号。可使用SIGNED 和UNSIGNED cast 操作符来覆盖它。将运算分别派给带符号或无符号64比特整数。
 (2)SELECT CAST(1-2 AS UNSIGNED)
直接赋值问题,采用了concat函数:concat(Str,’’)来变相的解决了

 最后的解决方法是:
 UPDATE tb_stuinfo SET stuName='王维',stuSex='男',stuHomeAddr='山西祁县'WHERE id=1;


查询不重复记录

select distinct deptno from emp; //查询表emp中的deptno字段信息,并去掉表中的记录去掉重复后显示出来关键字distinct

排序和限制

mysql> select * from emp order by sal limit 2; //表emp按照sal升序排列,limit关键字后面的起始偏移量没有写,即默认情况为0,显示的行数为2,即只显示前两条记录

mysql> select * from emp order by sal limit 1,2; //表emp按照sal升序排列,limit关键字后面的起始偏移量为1,显示行数为2,即显示第一条后面紧接着的两条记录,即2、3条记录

聚合

常见的聚合函数有sum(求和)、count(*)(记录数)、max(最大值)、min(最小值)

mysql> select deptno,count(1) from emp group by deptno having count(1)>1; // 统计人数大于1的deptno,HAVING关键字表示对分类后的结果再进行条件的过滤,和WHERE的区别在于HAVING是对聚合后的结果进行过滤,而HAVING是在聚合前的过滤


mysql> select deptno from emp union all select deptno from dept; //将表emp中的deptno和表dept中的deptno记录合并到一起并显示

mysql> select deptno from emp union select deptno from dept; //将表emp中的deptno和表dept中的deptno记录合并到一起,并去除其中重复记录

mysql> select sal from emp union all select deptname from dept; //不同类型的记录合并方式

注意:带事物的查询 。

  (一) DML语句书写建议

  (1). DML语句不允许出现@number方式替代字段名称

  不合理的写法:

  UPDATE table_name SET @1=NOW() WHERE @2=1;

  正确的写法:

  UPDATE table_name SET column_name1=NOW() WHERE column_name2=1;

  (2). UPDATE OR DELETE 禁用LIMIT子句

  不合理的写法:

  UPDATE table_name SET column_name1=NOW() WHERE column_name2=1 LIMIT 1;

  正确的写法:

  UPDATE table_name SET column_name1=NOW() WHERE column_name2=1;

  (3). INSERT语句需要写清楚值和字段对应关系

  不合理的写法:

  INSERT INTO table_name VALUES(NOW(),DATE_ADD(NOW(),INTERVAL +1 DAY));

  正确的写法:

  INSERT INTO table_name(gmt_create,gmt_modify) VALUES(NOW(),DATE_ADD(NOW(),INTERVAL +1 DAY));

  (4). DML语句少用不确定性函数

  常见被大家使用的不确定性函数:UUID()、RAND()、SYSDATE()等函数,若无特殊用处之外,请以确定性函数替代之。

  (二) 大数据量的DELETE OR UPDATE

  可能出于某些原因和运营目的,需要对数据库中的数据进行大量的清理或更改某字段的值,分别举 二个示例:

  ① 网络专项整治的时期,需要删除大量含某些关键词的内容;

  ② 给符合某一条件(例如:等级,在线时长)的游戏玩家,赠送100~1000不等数量的游戏币;

  给出的2个数据修改需求示例,若是直接根据相关要求去做,一个是需要用到模糊查询,另一个数据更新条件也没有合理索引可用,为此可能造成表对象表级锁被长时间锁住,而且阻塞其他更改类型数据操作服务,所以我们不得不采用更合理的办法,建议如下步骤实施:

  ① 设计并创建一张表tmp_pk_data ,用于记录将要被修改记录的主键,及需要的相关信息;

  ② 优先考虑在备库上跑一条SQL命令或存储过程的方式,把主键及相关数据写到表tmp_pk_data中;

  ③ 编写一个存储过程,使用游标循环控制获得tmp_pd_data的信息,根据主键更新或删除目标表的数据,且建议此操作在备库上完成(注释:必须是双主复制模式,才可在备库上执行);

  (三) 定期规律性清理数据的DELETE

  定期规律性数据的清理,优先对目标表的数据操纵方式进行分类:

  ① 若是日志类型的数据,则完全可以改为借助分区表的方式,比如按日期删除数据的条件,则可以用日期作为数据分区条件,然后增删分区的方式实现数据的清理工作;

  ② 若是数据的UPDATE/DELETE/SELECT操纵条件,与定期清理数据的规则一致或被其包含,则可以考虑使用分区表,然后借助删除分区方式达到数据清理的目标;

  ③ 若不能使用分区表解决的,则可以考虑参考上章节介绍的“大数据量的DELETE OR UPDATE”内容;

  (四) M-M架构的大数据量DML技巧

  定期规律性数据的清理,优先对目标表的数据操纵方式进行分类:

  ① 若是日志类型的数据,则完全可以改为借助分区表的方式,比如按日期删除数据的条件,则可以用日期作为数据分区条件,然后增删分区的方式实现数据的清理工作;

  ② 若是数据的UPDATE/DELETE/SELECT操纵条件,与定期清理数据的规则一致或被其包含,则可以考虑使用分区表,然后借助删除分区方式达到数据清理的目标;

  ③ 若不能使用分区表解决的,则可以考虑参考上章节介绍的“大数据量的DELETE OR UPDATE”内容;