ITPUB论坛-中国专业的IT技术社区

 找回密码
 注册
查看: 1400|回复: 8

[笔记] Mysql槽点

[复制链接]
论坛徽章:
73
2010新春纪念徽章
日期:2010-03-01 11:06:132011新春纪念徽章
日期:2011-02-18 11:43:332012新春纪念徽章
日期:2012-01-04 11:55:422013年新春福章
日期:2013-02-25 14:51:242014年新春福章
日期:2014-02-18 16:44:08马上有车
日期:2015-01-19 09:45:33最佳人气徽章
日期:2012-03-13 17:39:18ITPUB季度 技术新星
日期:2012-11-27 10:16:10问答徽章
日期:2013-10-23 15:13:22林肯
日期:2013-11-02 08:34:46
跳转到指定楼层
1#
发表于 2017-2-28 17:05 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
本帖最后由 XQKA 于 2018-3-25 18:57 编辑

Terrible choices: MySQL
https://blog.ionelmc.ro/2014/12/28/terrible-choices-mysql/

1.mysql视图不能用子查询

mysql 5.0之前都不支持视图的,mysql当前的最新版本都不支持物化视图。
create or replace view v_ds_cell_name as
select
  ds_cell.cell_code,
  substring_index(substring_index(ds_cell.synonyms, ';', tmp_lines.n), ';', -1) cell_name
from
  (select 1 n union all
   select 2 union all select 3 union all
   select 4 union all select 5 union all
   select 6 union all select 7) tmp_lines inner join ds_cell
  on char_length(ds_cell.synonyms) -char_length(replace(ds_cell.synonyms, ';', ''))>=tmp_lines.n-1
order by
  cast(ds_cell.cell_code as unsigned int), n;

报错:[Err] 1349 - View's SELECT contains a subquery in the FROM clause

处理方法:把子查询也做成一个视图
(1)
create or replace view v_lines as
select 1 n union all
   select 2 union all select 3 union all
   select 4 union all select 5 union all
   select 6 union all select 7;

(2)
create or replace view v_ds_cell_name as
select
  ds_cell.cell_code,
  substring_index(substring_index(ds_cell.synonyms, ';', v_lines.n), ';', -1) cell_name
from v_lines inner join ds_cell
  on char_length(ds_cell.synonyms) -char_length(replace(ds_cell.synonyms, ';', ''))>=v_lines.n-1
order by
  cast(ds_cell.cell_code as unsigned int), n;



2.Mysql的子查询必须要定义别名?

root@localhost proj1_ddb 09:09:32 > select count(1) from (select distinct * from t1);
ERROR 1248 (42000): Every derived table must have its own alias
root@localhost proj1_ddb 09:09:55 > select count(1) from (select distinct * from t1) t2;
+----------+
| count(1) |
+----------+
|   6982 |
+----------+
1 row in set (0.02 sec)

3. in和not in这个问题太奇怪了,没明白,not in不是应该有303行记录的吗?
select count(1) from  (select  distinct tumor_sample_barcode from ccle_cbiop_mutations_extended) t;
+----------+
| count(1) |
+----------+
|    905|
+----------+
1 row in set (0.40 sec)

select count(1) from (
-> select  distinct tumor_sample_barcode from ccle_cbiop_mutations_extended
-> where tumor_sample_barcode not in (select ccle_synonyms from proj1_ddb.ds_cell)) t;
+----------+
| count(1) |
+----------+
|     0 |
+----------+
1 row in set (0.16 sec)

select count(1) from (
-> select  distinct tumor_sample_barcode from ccle_cbiop_mutations_extended
-> where tumor_sample_barcode in (select ccle_synonyms from proj1_ddb.ds_cell)) t;
+----------+
| count(1) |
+----------+
|   602 |
+----------+
1 row in set (10.12 sec)

4.union合并的sql不能使用order by
虽然union里的sql用order by没什么意义,但不至于报错吧。
如下,order by必须注释掉,否则报错
[Err] 1221 - Incorrect usage of UNION and ORDER BY
select k_gene.gene_id AS gene_id,substring_index(substring_index(k_gene.aka,';',v_lines.n),',',-(1)) AS aka
from (v_lines join k_gene on(((char_length(k_gene.aka) - char_length(replace(k_gene.aka,',',''))) >= (v_lines.n - 1)))) /*order by cast(gene_id as unsigned int) */
UNION
select gene_id as gene_id,official_symbol as aka from k_gene;


5.空格被忽略了?这是什么梗
root@localhost testdb_01 12:39:00 > create table t1(sq int not null auto_increment,name varchar(64),primary key (sq));
Query OK, 0 rows affected (0.08 sec)

root@localhost testdb_01 15:43:35 > insert into t1 values (null,'PCI-2603');
Query OK, 1 row affected (0.00 sec)

root@localhost testdb_01 15:44:26 > insert into t1 values (null,'PCI-2603 ');
Query OK, 1 row affected (0.00 sec)

root@localhost testdb_01 15:44:41 > select * from t1;
+----+-----------+
| sq | name    |
+----+-----------+
|  1 | PCI-2603  |
|  2 | PCI-2603  |
+----+-----------+
2 rows in set (0.00 sec)

root@localhost testdb_01 15:44:46 > select * from t1 where name='PCI-2603';
+----+-----------+
| sq | name    |
+----+-----------+
|  1 | PCI-2603  |
|  2 | PCI-2603  |
+----+-----------+
2 rows in set (0.00 sec)
这个有点毁三观啊



6.text字段类型不能建立索引
root@localhost dsppb_clinvar 12:06:20 > create index idx_1_clinvar_xml_rawdata on clinvar_xml_rawdata (xml_data_value);
ERROR 1170 (42000): BLOB/TEXT column 'xml_data_value' used in key specification without a key length

7.不支持集合运算INTERSECT和MINUS


8.LIKE不区分大小写
环境:mysql 版本5.6.16,mysql参数:lower_case_table_names=1
select * from brca_pro_0703 where pdot like '%X' or pdot like '%x';
select * from brca_pro_0703 where pdot like '%x';

9 between ... and ... 对于整数而言是全闭包,对于float而言是半闭包
create table t1 (vf float);
insert into t1 values (0.1);
insert into t1 values (0.2);
insert into t1 values (0.3);
insert into t1 values (0.4);
insert into t1 values (0.5);
select * from t1 where vf between 0.1 and 0.3;
+------+
| vf   |
+------+
|  0.1 |
|  0.2 |
+------+
2 rows in set (0.00 sec)

create table t2 (vn int);
insert into t2 values (1);
insert into t2 values (2);
insert into t2 values (3);
insert into t2 values (4);
insert into t2 values (5);
select * from t2 where vn between 1 and 3;
+------+
| vn   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

这尼玛随意的令人震惊啊




论坛徽章:
73
2010新春纪念徽章
日期:2010-03-01 11:06:132011新春纪念徽章
日期:2011-02-18 11:43:332012新春纪念徽章
日期:2012-01-04 11:55:422013年新春福章
日期:2013-02-25 14:51:242014年新春福章
日期:2014-02-18 16:44:08马上有车
日期:2015-01-19 09:45:33最佳人气徽章
日期:2012-03-13 17:39:18ITPUB季度 技术新星
日期:2012-11-27 10:16:10问答徽章
日期:2013-10-23 15:13:22林肯
日期:2013-11-02 08:34:46
2#
 楼主| 发表于 2017-2-28 17:05 | 只看该作者
本帖最后由 XQKA 于 2018-1-18 14:22 编辑

1)这个error: 1449报错有点奇葩
[root@Dev-Mysql datadump]# mysqldump -uroot -proot proj2_dev > proj2_dev20170320.sql
Warning: Using a password on the command line interface can be insecure.
mysqldump: Got error: 1449: The user specified as a definer ('liu3'@'%') does not exist when using LOCK TABLES
数据库proj2_dev是从别个服务器导过来的,两个服务器的用户不完全一致,导入的时候是仔细检查并修改函数或存储过程的DEFINER部分的,
今天想把
proj2_dev导出来,就遇到上面的报错了。'liu3'@'%'这个用户显然不在这台服务器上,百度Got error: 1449,发现大量的文档是说:缺什么,建什么,比如这里要新建'liu3'@'%',
这个我显然是不能接受的,为了解决一个数据库报错,而新建一个用户,没法接受。
又找到一篇文档,说修改
mysql.proc的,把报错的definer改成 'root'@'localhost',感觉这个挺靠谱,相当于缺谁删谁,缺是既定事实(库里没有),删是必然的。



select * from his_t180116 where concat(nm,c_amnt) not in (select concat(nm,c_amnt) from t180116);
的结果为空集,但是经检查

his_t180116表确实是有concat(nm,c_amnt)不存在于t180116的行,就是说t180116相对于his_t180116确实是有些新增的行,但是如上的sql找不来,结果是空集,
这是因为:
his_t180116表中nm,c_amnt这两个字段可空,改成not null才可以找出来

使用道具 举报

回复
论坛徽章:
73
2010新春纪念徽章
日期:2010-03-01 11:06:132011新春纪念徽章
日期:2011-02-18 11:43:332012新春纪念徽章
日期:2012-01-04 11:55:422013年新春福章
日期:2013-02-25 14:51:242014年新春福章
日期:2014-02-18 16:44:08马上有车
日期:2015-01-19 09:45:33最佳人气徽章
日期:2012-03-13 17:39:18ITPUB季度 技术新星
日期:2012-11-27 10:16:10问答徽章
日期:2013-10-23 15:13:22林肯
日期:2013-11-02 08:34:46
3#
 楼主| 发表于 2017-2-28 17:06 | 只看该作者
本帖最后由 XQKA 于 2017-10-12 14:16 编辑

mysql的 union语句不支持字句使用 order by,报错
[Err] 1221 - Incorrect usage of UNION and ORDER BY
这可能是union的过程中本来就是一个排序的过程

使用道具 举报

回复
论坛徽章:
73
2010新春纪念徽章
日期:2010-03-01 11:06:132011新春纪念徽章
日期:2011-02-18 11:43:332012新春纪念徽章
日期:2012-01-04 11:55:422013年新春福章
日期:2013-02-25 14:51:242014年新春福章
日期:2014-02-18 16:44:08马上有车
日期:2015-01-19 09:45:33最佳人气徽章
日期:2012-03-13 17:39:18ITPUB季度 技术新星
日期:2012-11-27 10:16:10问答徽章
日期:2013-10-23 15:13:22林肯
日期:2013-11-02 08:34:46
4#
 楼主| 发表于 2017-2-28 17:07 | 只看该作者
占位3

使用道具 举报

回复
论坛徽章:
73
2010新春纪念徽章
日期:2010-03-01 11:06:132011新春纪念徽章
日期:2011-02-18 11:43:332012新春纪念徽章
日期:2012-01-04 11:55:422013年新春福章
日期:2013-02-25 14:51:242014年新春福章
日期:2014-02-18 16:44:08马上有车
日期:2015-01-19 09:45:33最佳人气徽章
日期:2012-03-13 17:39:18ITPUB季度 技术新星
日期:2012-11-27 10:16:10问答徽章
日期:2013-10-23 15:13:22林肯
日期:2013-11-02 08:34:46
5#
 楼主| 发表于 2017-2-28 17:07 | 只看该作者
占位4

使用道具 举报

回复
论坛徽章:
73
2010新春纪念徽章
日期:2010-03-01 11:06:132011新春纪念徽章
日期:2011-02-18 11:43:332012新春纪念徽章
日期:2012-01-04 11:55:422013年新春福章
日期:2013-02-25 14:51:242014年新春福章
日期:2014-02-18 16:44:08马上有车
日期:2015-01-19 09:45:33最佳人气徽章
日期:2012-03-13 17:39:18ITPUB季度 技术新星
日期:2012-11-27 10:16:10问答徽章
日期:2013-10-23 15:13:22林肯
日期:2013-11-02 08:34:46
6#
 楼主| 发表于 2017-2-28 17:07 | 只看该作者
占位5

使用道具 举报

回复
论坛徽章:
73
2010新春纪念徽章
日期:2010-03-01 11:06:132011新春纪念徽章
日期:2011-02-18 11:43:332012新春纪念徽章
日期:2012-01-04 11:55:422013年新春福章
日期:2013-02-25 14:51:242014年新春福章
日期:2014-02-18 16:44:08马上有车
日期:2015-01-19 09:45:33最佳人气徽章
日期:2012-03-13 17:39:18ITPUB季度 技术新星
日期:2012-11-27 10:16:10问答徽章
日期:2013-10-23 15:13:22林肯
日期:2013-11-02 08:34:46
7#
 楼主| 发表于 2017-2-28 17:09 | 只看该作者
个人笔记,误删6

使用道具 举报

回复
论坛徽章:
73
2010新春纪念徽章
日期:2010-03-01 11:06:132011新春纪念徽章
日期:2011-02-18 11:43:332012新春纪念徽章
日期:2012-01-04 11:55:422013年新春福章
日期:2013-02-25 14:51:242014年新春福章
日期:2014-02-18 16:44:08马上有车
日期:2015-01-19 09:45:33最佳人气徽章
日期:2012-03-13 17:39:18ITPUB季度 技术新星
日期:2012-11-27 10:16:10问答徽章
日期:2013-10-23 15:13:22林肯
日期:2013-11-02 08:34:46
8#
 楼主| 发表于 2017-2-28 17:10 | 只看该作者
个人笔记,勿要删除7

使用道具 举报

回复
论坛徽章:
73
2010新春纪念徽章
日期:2010-03-01 11:06:132011新春纪念徽章
日期:2011-02-18 11:43:332012新春纪念徽章
日期:2012-01-04 11:55:422013年新春福章
日期:2013-02-25 14:51:242014年新春福章
日期:2014-02-18 16:44:08马上有车
日期:2015-01-19 09:45:33最佳人气徽章
日期:2012-03-13 17:39:18ITPUB季度 技术新星
日期:2012-11-27 10:16:10问答徽章
日期:2013-10-23 15:13:22林肯
日期:2013-11-02 08:34:46
9#
 楼主| 发表于 2017-2-28 17:11 | 只看该作者
勿插队8
见谅

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表
http://www.vxiaotou.com