数据库查询

Question

数据库内有表 table1 表内有字段time , aaa字段   格式如下  time aaa   
10:01 22
10:02 34
10:03 43
10:04 44
10:05 44
10:06 32
10:07 5
10:08 32
10:10 33
10:11 100
...................
15:20 236时间是连续的且再更新，在查询的那一时刻判断aaa的值是不是aaa内所有数据最大值或最小值 ，如果是是则找出这个时间

Answer 1

最大值
select * from table1 order by aaa desc limit 1;最小值
select * from table1 order by aaa limit 1;

Answer 2

select * from table1 a 
where not exists (select 1 from table1 where aaa<a.aaa)
or not exists (select 1 from table1 where aaa>a.aaa)

Answer 3

SELECT * FROM table1 
WHERE aa = (SELECT MIN(aa) FROM table1)
    or aa = (SELECT MAX(aa) FROM table1)
如果在aa列上有索引的话，这个查询的执行速度会很快，因为两个子查询返回的最大和最小值直接就可以从索引中取出，不需要扫描表和索引~
但是添加索引会使增删改的速度受到影响，LZ需要权衡一下利弊~

Answer 4

呵呵，像这样：
ALTER TABLE table1 ADD INDEX `index_aaa`(`aaa`);

Answer 5

sorry~说错了平时都用MySQL Browser改表结构，不怎么写语句的见谅~
是这样：ALTER TABLE table1 ADD INDEX index_aaa(aaa);

Answer 6

出错了，BLOB/TEXT column 'aaa' used in key specification without a key length  

Answer 7

aaa列是BLOB/TEXT类型的？！我以为是INT~
可是LZ为什么要对文本取最大最小值呢？
如果必须对文本取最大最小值，那即使建立了有长度的索引也毫无帮助~
LZ还是确认一下，为什么那一列都是数字，列类型却是BLOB/TEXT类型~

Answer 8

但是确实可以执行数字之和之类的操作。SELECT * FROM table1  
WHERE aa = (SELECT MIN(aa) FROM table1)
  or aa = (SELECT MAX(aa) FROM table1)这个也能执行

Answer 9

是的，用字符串存储数字，也可以进行数学计算，不过这是非常不好的设计；
如果aaa列是VARCHAR类型，也能通过建立索引使这个查询更高效；
只是BLOB/TEXT类型无法对所有数据建立索引，而建立前缀索引在这个查询语句里一点帮助也没有；
不更改表结构的话，这个查询只能这样了--没有索引

Answer 10

如果有好几百张表的话，把列的类型修改了，对应用程序的影响可能非常大，所以这将是一个巨大而严谨的工程，需要小心的做这个决定；而且工作量也比较大，比较麻烦。如果LZ只是单纯的想通过完成这么一个工程，来提高这个查询的执行速度，可能有些太不靠谱了；如果楼主就是想改变这个不合适的架构，那么可以考虑下面这个方法：（仅供参考，LZ要三思而后行）
因为要根据每个表的数据情况来确定列长度。先根据应用程序的实际情况来大致确定aaa需要多大，比如INT类型就足够大了，那么这个VARCHAR列的最小长度就是12。因为列的旧类型是TEXT，因此要对每个表执行SELECT MAX(CHAR_LENGTH(aaa)) FROM tableName,来得到实际数据中最大长度的数据是多少，如果超过12个字符，更改列类型会失败。如果运气足够好，所有表的这列的最大长度都小于13，那么就逐个执行ALTER TABLE tableName MODIFY COLUMN aaa VARCHAR(12)。如果运气不好，有些已经存在的数据在这一列超长，说明这些数据是错误的数据（前提是你确定正确的数据不会超出INT的范围），你可以更改这些错误数据使它们的长度小于13，然后再ALTER TABLE。以上这些工作可以通过写脚本来执行。
列类型改成CARCHAR()后，就可以添加索引了~

Answer 11

补充一下，我说INT类型改成字符串需要12个字符是指在32位的系统上，其实不需要用INT衡量的。
比如aaa的取值在0~9999之间，那么4个字符就够了，如果是-9999~9999，就需要5个字符~

Answer 12

再补充一下啊，关于有数据超长的问题，如果应用程序的需求不允许更改当前存在的数据，那就只能以需求为准--不更改数据，这时就比较麻烦。如果要求所有这些表的列的结构必须完全一样，那惨了，只能以所有表中所有aaa列数据的最长的那个为最大长度；如果允许这几百张表的结构不一样，那就可以以每张表的最大列长度来为各自设置一个长度大于等于12的VARCAHR。