MySQL使用临时表导致的bug

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我需要很长长长长长]

结果被截断

３．总结

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

转载于:https://my.oschina.net/maoren/blog/739040