文档章节

MySQL使用临时表导致的bug

秦maoren
 秦maoren
发布于 2016/08/28 23:06
字数 1654
阅读 123
收藏 0
  1. mysql 合适使用临时表
1、UNION查询;
2、用到TEMPTABLE算法或者是UNION查询中的视图;
3、ORDER BY和GROUP BY的子句不一样时;
4、表连接中,ORDER BY的列不是驱动表中的;
5、DISTINCT查询并且加上ORDER BY时;
6、SQL中用到SQL_SMALL_RESULT选项时;
7、FROM中的子查询;
8、子查询或者semi-join时创建的表;

2. 案例重现

2.1 准备数据库schema

CREATE TABLE `workbench_attr_entry` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `shop_id` varchar(100) COLLATE utf8_bin NOT NULL,
  `attr_name` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '属性',
  `attr_id` bigint(20) NOT NULL,
  `attr_item_column` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '属性值对应的字段',
  `disabled` tinyint(1) DEFAULT NULL COMMENT '是否禁用',
  `create_at` datetime DEFAULT NULL COMMENT '生成时间',
  `update_at` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `workbench_attr_entry_uk` (`shop_id`,`attr_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='客服工作台数据-店铺&属性表';
CREATE TABLE `workbench_attr_item_entry` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `shop_id` varchar(100) COLLATE utf8_bin NOT NULL,
  `buyer_nick` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '客户ID',
  `attr_id` bigint(20) DEFAULT NULL COMMENT '属性ID',
  `date_value` date DEFAULT NULL COMMENT '时间类型属性值',
  `string_value` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '字符类型属性值',
  `double_value` decimal(20,4) DEFAULT NULL COMMENT '数值类型属性值',
  `create_at` datetime DEFAULT NULL COMMENT '生成时间',
  `update_at` datetime DEFAULT NULL COMMENT '更新时间',
  `dic_value` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `workbench_attr_item_entry_uk` (`shop_id`,`buyer_nick`,`attr_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='店铺&属性&item表';

-- 插入数据
insert into workbench_attr_entry(shop_id,attr_id,attr_name) values(100571094,1,'属性1');
insert into workbench_attr_entry(shop_id,attr_id,attr_name) values(100571094,2,'属性2');
insert into workbench_attr_entry(shop_id,attr_id,attr_name) values(100571094,3,'属性3');
insert into workbench_attr_entry(shop_id,attr_id,attr_name) values(100571094,4,'属性4');
insert into workbench_attr_entry(shop_id,attr_id,attr_name) values(100571094,5,'属性5');
insert into workbench_attr_entry(shop_id,attr_id,attr_name) values(100571094,5,'属性5');
insert into workbench_attr_entry(shop_id,attr_id,attr_name) values(100571094,6,'属性6');



insert into workbench_attr_item_entry(shop_id,buyer_nick,attr_id,string_value) values (100571094,'nick',1,'属性1,我需要很长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长');
insert into workbench_attr_item_entry(shop_id,buyer_nick,attr_id,string_value) values (100571094,'nick',2,'属性2,我需要很长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长');
insert into workbench_attr_item_entry(shop_id,buyer_nick,attr_id,string_value) values (100571094,'nick',3,'属性3,我需要很长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长');
insert into workbench_attr_item_entry(shop_id,buyer_nick,attr_id,string_value) values (100571094,'nick',4,'属性4我需要很长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长');
insert into workbench_attr_item_entry(shop_id,buyer_nick,attr_id,string_value) values (100571094,'nick',5,'属性5,我需要很长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长');
insert into workbench_attr_item_entry(shop_id,buyer_nick,attr_id,string_value) values (100571094,'nick',6,'属性4我需要很长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长');
insert into workbench_attr_item_entry(shop_id,buyer_nick,attr_id,string_value) values (100571094,'nick',7,'属性5,我需要很长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长');

2.1 使用普通sql查询未使用到临时表

Select a.buyer_nick , concat('[', group_concat(concat( '{ \'attr_name\': \'', b.attr_name,'\', ', '\'attr_value\': \'', ifnull(a.date_value, ''), ifnull(a.string_value, ''), ifnull(a.double_value,''), '\'}' )) , ']')  as attrs  from workbench_attr_item_entry a  inner join workbench_attr_entry b on a.shop_id = b.shop_id and a.attr_id = b.attr_id  where a.shop_id = 100571094  Group by a.buyer_nick

结果:

| nick       | [{ 'attr_name': '属性1', 'attr_value': '属性1,我需要很长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长'},{ 'attr_name': '属性3', 'attr_value': '属性3,我需要很长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长'},{ 'attr_name': '属性5', 'attr_value': '属性5,我需要很长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长'},{ 'attr_name': '属性2', 'attr_value': '属性2,我需要很长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长'},{ 'attr_name': '属性4', 'attr_value': '属性4我需要很长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长'}]               

2.2 使用到临时表sql查询


select * from ( Select a.buyer_nick , concat('[', group_concat(concat( '{ \'attr_name\': \'', b.attr_name,'\', ', '\'attr_value\': \'', ifnull(a.date_value, ''), ifnull(a.string_value, ''), ifnull(a.double_value,''), '\'}' )) , ']')  as attrs  from workbench_attr_item_entry a  inner join workbench_attr_entry b on a.shop_id = b.shop_id and a.attr_id = b.attr_id  where a.shop_id = 100571094  Group by a.buyer_nick ) t limit 0, 20

结果:

| nick       | [{ 'attr_name': '属性6', 'attr_value': '属性4我需要很长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长'},{ 'attr_name': '属性1', 'attr_value': '属性1,我需要很长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长'},{ 'attr_name': '属性3', 'attr_value': '属性3,我需要很长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长'},{ 'attr_name': '属性5', 'attr_value': '属性5,我需要很长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长'},{ 'attr_name': '属性2', 'attr_value': '属性2,我需要很长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长'},{ 'attr_name': '属性4', 'attr_value': '属性4我需要很长长长长长]

结果被截断

3.总结

mysql在使用临时表特别注意长度.如果程序封装分页查询时需要注意字段截取问题,如何配置临时表字段长度,后续研究给出,如果已知请评论备注,谢谢

© 著作权归作者所有

秦maoren
粉丝 3
博文 2
码字总数 2061
作品 0
徐汇
私信 提问
MySQL · 捉虫动态 · 5.6 与 5.5 InnoDB 不兼容导致 crash

bug 背景 RDS的备份工具用的是 Percona-XtraBackup(后面简称PXB),这个工具包里有2个重要的工具,innobackupex和xtrabackup,后者是C编译出的二进制文件,负责备份 InnoDB 数据,前者是一个...

阿里云RDS-数据库内核组
2015/05/04
0
0
【MySql】关于临时表cann't reopen

当你创建临时表的时候,你可以使用temporary关键字。如:   create temporary table tmp_table(name varchar(10) not null,passwd char(6) not null)‘ 或 CREATE TEMPORARY TABLE IF NOT ......

磊神Ray
2012/06/14
111
0
【MySQL】【翻译】 MySQL 5.7 的内部临时表新特性

【MySQL】【翻译】MySQL Internal Temporary Tables in MySQL 5.7(MySQL 5.7 内部临时表) Alexander Rubin | December 4, 2017 | Posted In: Insight for DBAs, MySQL, Percona Monitorin......

对唔住
2018/06/26
0
0
MySQL · 捉虫状态 · bug分析两例

BUG 1 IN查询结果不对 背景 在mysql5.6.16版本下,构建如下测试用例 从例子可以看到当tmptablesize=262144时,查询结果不对,而tmptablesize=1610241024时查询结果是正确的。 分析 查询结果跟...

阿里云RDS-数据库内核组
2016/09/07
0
0
深度分析 | JDBC与MySQL临时表空间的分析

原创作者:秦沛、胡呈清 背景 应用 JDBC 连接参数采用 useCursorFetch=true,查询结果集存放在 mysqld 临时表空间中,导致ibtmp1 文件大小暴增到90多G,耗尽服务器磁盘空间。为了限制临时表空...

爱可生
07/02
15
0

没有更多内容

加载失败,请刷新页面

加载更多

关于运维,该怎么决定它的方向,这个似工作又似兴趣的存在

我之前主要从事网络、桌面、机房管理等相关工作,这些工作使我迷惘,这应该是大多数运维人都经历过的过程; 18年国庆,我从国内前三的消费金融公司裸辞,下海创业,就是想要摆脱这样的困境。...

网络小虾米
28分钟前
5
0
Java Timer的用法

Timer timer = new Timer(); timer.schedule(new TimerTask() { public void run() { System.out.println("11232"); } }, 200000 , 1000); public void schedule(TimerTask task, long delay......

林词
32分钟前
5
0
使用js动态加载外部js文件以及动态创建script脚本

动态脚本指的是在页面加载时不存在,但将来的某一时刻通过修改该DOM动态添加的脚本。和操作HTML元素一样,创建动态脚本也有两种方式:插入外部文件和直接插入JavaScript代码。 动态加载外的外...

Bing309
39分钟前
3
0
从零开始入门 K8s | Kubernetes 网络概念及策略控制

作者 | 阿里巴巴高级技术专家 叶磊 一、Kubernetes 基本网络模型 本文来介绍一下 Kubernetes 对网络模型的一些想法。大家知道 Kubernetes 对于网络具体实现方案,没有什么限制,也没有给出特...

阿里巴巴云原生
43分钟前
3
0
天气获取

本文转载于:专业的前端网站➨天气获取 $.get("http://wthrcdn.etouch.cn/WeatherApi", { citykey: cityCode }, function (d) { //创建文档对象 var parser = new ......

前端老手
43分钟前
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部