MySQL记录 #28

lovecn · 2015-06-01T13:32:13Z

mysql> select *from history;
+----+---------------------+-----+-----+
| id | time                | uid | num |
+----+---------------------+-----+-----+
|  1 | 2015-05-01 11:11:11 |   1 |   1 |
|  2 | 2015-05-02 11:11:11 |   1 |   2 |
|  3 | 2015-05-03 11:11:11 |   1 |   3 |
|  4 | 2015-05-01 11:11:11 |   2 |   1 |
|  5 | 2015-05-02 11:11:11 |   2 |   2 |
|  6 | 2015-05-03 11:11:11 |   2 |   3 |
+----+---------------------+-----+-----+
//uid的历史记录中时间最大的记录
mysql> select a.*from (select *from history where uid in(1,2)) a inner join (select max(time) max_time ,uid from history where uid in (1,2) group by uid) b where a.uid=b.uid and a.time =b.max_time;
+----+---------------------+-----+-----+
| id | time                | uid | num |
+----+---------------------+-----+-----+
|  3 | 2015-05-03 11:11:11 |   1 |   3 |
|  6 | 2015-05-03 11:11:11 |   2 |   3 |
+----+---------------------+-----+-----+
2 rows in set (0.00 sec)


mysql test < 91620_all.sql
ERROR 2006 (HY000) at line 17128: MySQL server has gone away
上面可以看到，文件大小为27M导入的时候会报这个错误。
通过修改MySQL配置my.cnf文件，在最后一行增加max_allowed_packet=32M就可以了



如果数据类型是 varchar 的数字，在排序的过程中，出来并不是我们想要的效果。
例如：

select * from table order by field_varchar ;
field_varchar
0
1
11
100
2
22
3
33
在网上查找了一圈，比较常见的做法是：

select * from table order by field_varchar+0;
转换为数字

field_varchar
0
1
2
3
11
22
33
100
点进来看的都是有求知欲的，所以对的，故事还没有完。
如果 这个字段里面有 空字符窜 或者 null。上面的方法还不够完美。

会出现下面的情况

field_varchar
0
空
1
2
3
11
22
33
100
因为空字符窜+0 是0， 0+0 也是0 ，这个时候就会出现不稳定的 0 和空 上下跳动的情况。
那么真正正确的姿态是怎么样的呢？

请看大屏幕

select * from table order by length(field_varchar),field_varchar;

查找表中多余的重复记录（多个字段）

SELECT * FROM clerk a
WHERE (a.id, a.name) IN 
(SELECT id, name FROM clerk GROUP BY id, name HAVING COUNT(*) > 1);
要删除多余的重复记录的话，将select *　改成delete就行了

随机查询Ｎ条数据

通常使用rand()函数来实现，但需要注意的是，在ORDER BY从句里面不能使用RAND()函数，因为这样会导致数据列被多次扫描，如此一来效率就会很慢。
使用where子句实现：

SELECT * FROM table_name
WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM table_name)) )
ORDER BY id LIMIT 1;
使用join实现(获取的Ｎ条数据连续)：

SELECT * FROM table_name AS t1 JOIN 
(SELECT ROUND(RAND() * ((SELECT MAX(id) FROM table_name)-(SELECT MIN(id) FROM table_name))
　+(SELECT MIN(id) FROM table_name)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 1;
使用join实现(获取的Ｎ条数据不连续)：

SELECT * FROM table_name AS t1 JOIN 
(SELECT ROUND(RAND() * ((SELECT MAX(id) FROM table_name)-(SELECT MIN(id) FROM table_name))+　(SELECT MIN(id) FROM table_name)) AS id from table_name limit 50) AS t2 
on t1.id=t2.id
ORDER BY t1.id LIMIT 1;
查询某个字段是否包含某子串

最简单的方法是使用LIKE来查询

SELECT * FROM  table_name WHERE name LIKE '%keyword%';
可以使用locate, instr函数来代替LIKE查询，速度也会快一些
locate函数的用法是locate(substr, str, pos)
locate、position 和 instr 的差別只是参数的位置不同，同时locate 多一个请始位置的参数外，两者是一样的

SELECT * FROM table_name WHERE locate('keyword', name)>0;
SELECT * FROM table_name WHERE position('keyword' IN name);
SELECT * FROM table_name WHERE instr(name, 'keyword')>0;

MySQL无法保存𡘾 http://www.zdic.net/z/89/js/2163E.htm
提示Warning: #1366 Incorrect string value: '\xF0\xA1\x98\xBE' for column 'word' at row 1
搞定如下
alter table comments default character set=utf8mb4;
ALTER TABLE comments CHANGE word word TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL ;
set names utf8mb4; 
UPDATE comments SET word=''
ALTER DATABASE foo CHARACTER SETutf8mb4 COLLATE utf8mb4_general_ci 
mysql> SELECT x'4D7953514C';
-> 'MySQL'
mysql> SELECT 0x4D7953514C;
-> 'MySQL'

CASE： 
统计呼叫中心客服每一天的通话数量及时长 
DESCRIPTION： 
每天每位客服，只产生一条记录来统计通话时长和次数，这样就需要，相应记录存在就更新，不存在就插入 
SOLUTION： 
INSERT INTO kjrs_crm2.crm_wavtime(time_info,number_info,ext,update_time)VALUES(‘26’,’2’,’812’,’1433433600’),(‘1395’,’4’,’820’,’1433433600’),(‘190’,’2’,’975’,’1433433600’) ON DUPLICATE KEY UPDATE time_info=VALUES(time_info),number_info=VALUES(number_info),ext=VALUES(ext),update_time=VALUES(update_time) 
NOTICE： 
还有其它解决方法，但是都没有INSERT INTO … ON DUPLICATE KEY UPDATE …更好； 
REPLACE INTO ： 如果存在已有记录，将先删除，再插入新数据，效率低，同时会使主键id不断增大； 
先SELECT，再确定UPDATE还是INSERT INTO： 效率低，代码量大


Emoji 字符的特殊之处是，在存储时，需要用到 4 个字节。而 MySQL 中常见的 utf8 字符集的 utf8_general_ci 这个 collate 最大只支持 3 个字节。所以为了能够存储 Emoji，你需要改用 utf8mb4 字符集。

在创建表时，用类似这样的语句：

CREATE TABLE `tbl` (...) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;
MySQL 版本

对 utf8mb4 字符集的支持是 MySQL 5.5 的新功能，所以你需要确保你使用的 MySQL 版本至少是 5.5。基本上，2014 年以后的新项目都应该直接上 5.6 了。

MySQL 备份和导入

在启用了 utf8mb4 字符集之后，备份和导入时就不能再用默认参数了。

用 mysqldump 备份时，需要加入：

mysqldump --default-charater-set=utf8mb4
而在恢复备份或通过程序连接时，需要在每次连接打开之后发送下面这条 SQL 指令：

SET CHARSET utf8mb4

建议有表情的字段还用varchar，存之前pickle.dumps一下，用之前pickle.loads一下
阿里云上的RDS之前没升级到5.5，又要支持emoji，结果只好把字符串base64一下存，取出来的时候再base64一次

对于innodb引擎而言，如果没有显式声明主键，MYSQL会自动生成一个主键
在线sql http://sqlfiddle.com/#!2/7d8769/11

 select sex,max(score) as score from score group by score; 能否不用子查询就把name也查出来？
SELECT name, sex, max(score) AS score FROM score GROUP BY sex; group by是对sex聚合分组，这样取出来的name是不对应score的。
在SELECT语句中，所有不出现在聚合函数（MAX）的列名都必须出现在GROUP BY中。 
但是写GROUP BY name显然不是想要的结果，所以应该只能写子查询

select * from (select t.sex, max(t.score) as score from score t group by sex) aa, score bb where bb.sex = aa.sex and bb.score = aa.score
SELECT 
s1.sex, 
s2.NAME, 
s1.score 
FROM 
( 
SELECT 
sex, 
max(score) AS score 
FROM 
score 
GROUP BY 
sex 
) s1 
LEFT JOIN score s2 ON s1.score = s2.score 
ORDER BY s1.score DESC

error 根据name分组，然后修改对应id最大的那条将age修改成1
$ update test set t.age=1 where t.id in(select max(t1.id) from test t1 group by t1.name)
https://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query#1262848
UPDATE 表 set age = 1 where id in ( 
select a.id from (SELECT max(id) as id FROM 表 GROUP BY name) a );

UPDATE table1 dest, (SELECT * FROM table2 where id=x) src 
  SET dest.col1 = src.col1 where dest.id=x ;

MySQL也能利用索引来快速地执行ORDER BY和GROUP BY语句的排序和分组操作。

通过索引优化来实现MySQL的ORDER BY语句优化：

1、ORDER BY的索引优化。如果一个SQL语句形如：
SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort];
在[sort]这个栏位上建立索引就可以实现利用索引进行order by 优化。

2、WHERE + ORDER BY的索引优化，形如：
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort];
建立一个联合索引(columnX,sort)来实现order by 优化。

注意：如果columnX对应多个值，如下面语句就无法利用索引来实现order by的优化
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort];

3、WHERE+ 多个字段ORDER BY
SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;
建立索引(uid,x,y)实现order by的优化,比建立(x,y,uid)索引效果要好得多。

MySQL Order By不能使用索引来优化排序的情况
* 对不同的索引键做 ORDER BY ：(key1,key2分别建立索引)
SELECT * FROM t1 ORDER BY key1, key2;

* 在非连续的索引键部分上做 ORDER BY：(key_part1,key_part2建立联合索引;key2建立索引)
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;

* 同时使用了 ASC 和 DESC：(key_part1,key_part2建立联合索引)
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

* 用于搜索记录的索引键和做 ORDER BY 的不是同一个：(key1,key2分别建立索引)
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

* 如果在WHERE和ORDER BY的栏位上应用表达式(函数)时，则无法利用索引来实现order by的优化
SELECT * FROM t1 ORDER BY YEAR(logindate) LIMIT 0,10;

特别提示:
1>mysql一次查询只能使用一个索引。如果要对多个字段使用索引，建立复合索引。
2>在ORDER BY操作中，MySQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。
float,double容易产生误差，对精确度要求比较高时，建议使用decimal来存，decimal在mysql内存是以字符串存储的，用于定义货币要求精确度高的数据。在数据迁移中，float(M,D)是非标准定义，最好不要这样使用。M为精度，D为标度。

mysql> create table t1(c1 float(10,2), c3 decimal(10,2)); 
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values(1234567.23, 1234567.23);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------------+------------+
| c1 | c3 |
+------------+------------+
| 1234567.25 | 1234567.23 | 
+------------+------------+
1 row in set (0.02 sec)

mysql> insert into t1 values(9876543.21, 9876543.12);
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> select * from t1;
+------------+------------+
| c1 | c3 |
+------------+------------+
| 1234567.25 | 1234567.23 | 
| 9876543.00 | 9876543.12 | 
+------------+------------+
2 rows in set (0.00 sec)

不定义fload, double的精度和标度时，存储按给出的数值存储，这于OS和当前的硬件有关。

decimal默认为decimal(10,0)

因为误差问题，在程序中，少用浮点数做=比较，可以做range比较。如果数值比较，最好使用decimal类型。

精度中，符号不算在内：

mysql> insert into t1 values(-98765430.21, -98765430.12);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+--------------+--------------+
| c1 | c3 |
+--------------+--------------+
| 1234567.25 | 1234567.23 | 
| 9876543.00 | 9876543.12 | 
| -98765432.00 | -98765430.12 | 
+--------------+--------------+
3 rows in set (0.00 sec)


float占4个字节，double占8个字节，decimail(M,D)占M+2个字节。

mysql> create table t1 (i int, d1 double, d2 double);

mysql> insert into t1 values (2, 0.00  , 0.00),
                             (2, -13.20, 0.00),
                             (2, 59.60 , 46.40),
                             (2, 30.40 , 30.40);

mysql> select i, sum(d1) as a, sum(d2) as b from t1 group by i having a <> b;
+------+-------------------+------+
| i    | a                 | b    |
+------+-------------------+------+
|    2 | 76.80000000000001 | 76.8 |
+------+-------------------+------+
1 row in set (0.00 sec)

mysql> create table t2 (i int, d1 decimal(60,30), d2 decimal(60,30));
Query OK, 0 rows  affected (0.09 sec)

mysql> insert into t2 values (2, 0.00  , 0.00),
                             (2, -13.20, 0.00),
                             (2, 59.60 , 46.40),
                             (2, 30.40 , 30.40);
Query OK, 4 rows affected (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select i, sum(d1) as a, sum(d2) as b from t2 group by i having a <> b;
Empty set (0.00 sec)
The result as expected is empty set.
http://stackoverflow.com/questions/6831217/double-vs-decimal-in-mysql
double类型 0.1+0.2 会同js一样输出0.30000000000000004

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock'
由于mysql 默认的mysql.sock 是在/var/lib/mysql/mysql.sock，但linux系统总是去/tmp/mysql.sock查找，所以会报错
[root@localhost ~]# mysql --socket=/var/lib/mysql/mysql.sock
或者[root@localhost ~]# ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock