常用汇总

常用汇总

---

truncate 与 delete 区别

• truncate : ddl 语句，删除数据不可恢复；清除索引；表中自增 auto_increment = 0
• delete : dml 语句，删除数据可恢复，记录操作到日志中；表中自增 auto_increment 不受影响

参考资料

• drop、truncate和delete的区别

---

设置 auto_increment

• 查询当前表中自增基数，即下一条数据的主键值

SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name="db_table_name";

• 如果欲恢复的值已被占用，删掉这部分数据

delete from db_hd_activity where id  >= 500 ;

• 将数据表中数据的自增基数改为500，下一条数据主键500

ALTER TABLE tableName auto_increment = 500 ;

• 其他
  • information_schema数据库是MySQL自带的，它提供了访问数据库元数据的方式

参考资料

• MySQL查询和修改auto_increment的方法
• MySQL中information_schema是什么

---

语言分类

• DML(data manipulation language) 数据表数据操作语言
  • insert update delete
• DQL
  • select
• DCL(data control language) 修改用户角色权限
  • grant
• DDL(data definition language) 定义或改变表结构
  • create alter

参考资料

• DDL、DML和DCL的区别与理解

---

表结构设计

• 存储表情 ：表情列字符集为utf8mb4 ,字符序为 utf8mb4_general_ci

---

结果查询

• 当表中字段为varchar(1024) 可变字符串类型且长度大于256时，默认查询时select left(str,256) ，通过mybatis打印的SQL日志看默认查询长度为256
  • LEFT()
    • MySQL字符串函数，返回从字符串str最左边的len个字符，如果任一参数为null则返回null
  • 注意
    • 建表时需要考虑如何设计合理，是否需要将所有数据以逗号间隔放入一个字段中
    • 查询时需要考虑手动添加 LEFT(str,1024) 否则查询的结果将会丢失数据

---

表的别名

• 删除操作不允许使用表的别名

DELETE
FROM
database.db_test b
WHERE b.g_id IN (9998883);

---

count

select count(group_id)
from tqdb_base.db_hd_group where act_id in (813,839,854) and is_del = 1 ;

• Count() 挨着，count 与 ( 有空格，会报错

---

SQL错误

/* SQL错误（1241）：Operand should contain 1 column(s) */
/* 受影响行数: 0  已找到记录: 0  警告: 0  持续时间 0 of 1 查询: 0.000 sec. */

子查询中包含多个条件，应该只包含一个

Select * form a where a.id in (
	Select b.id ,b.name form b 
)

• 参考资料
  • Mysql语句报错Operand should contain * column解决办法

---

时间戳

-- 时间转时间戳，精确到秒，Java 中输出 new Date().getTime() 13 位
select unix_timestamp(now()) from dual ;
select unix_timestamp("2018-11-26 16:00:00") from dual ;

-- 时间戳转时间
select from_unixtime(1543219200) from dual ;

参考资料

• mysql 时间戳的使用!

---

查询条件

• Select * from table where id = “” 最左匹配查询条件，完全匹配 find_in_set(“查询内容”,字段名称)
• int 数值类型字段，与字符串查询条件匹配，只匹配第一个赋值，后续赋值省略

mysql> show tables ;
+-------------------+
| Tables_in_test    |
+-------------------+
| abc               |
| test              |
| user              |
+-------------------+
5 rows in set

mysql> select * from test ;
+----+------+-----+-----+---------------------+-------------+
| id | name | sex | age | create_time         | update_time |
+----+------+-----+-----+---------------------+-------------+
|  1 | 1    | 1   |   1 | 2018-07-29 10:36:35 | 2018-07-29  |
|  2 | 2    | 2   |   2 | 2018-07-29 10:36:48 | 2018-07-29  |
|  3 | 3    | 3   |   3 | 2018-07-29 10:37:01 | 2018-07-29  |
+----+------+-----+-----+---------------------+-------------+
3 rows in set

mysql> select * from test where id = "1,2,3" ;
+----+------+-----+-----+---------------------+-------------+
| id | name | sex | age | create_time         | update_time |
+----+------+-----+-----+---------------------+-------------+
|  1 | 1    | 1   |   1 | 2018-07-29 10:36:35 | 2018-07-29  |
+----+------+-----+-----+---------------------+-------------+
1 row in set

mysql> select * from test where id = "2,1,3" ;
+----+------+-----+-----+---------------------+-------------+
| id | name | sex | age | create_time         | update_time |
+----+------+-----+-----+---------------------+-------------+
|  2 | 2    | 2   |   2 | 2018-07-29 10:36:48 | 2018-07-29  |
+----+------+-----+-----+---------------------+-------------+
1 row in set

mysql> select * from test where find_in_set("1,2,3",id) ;
Empty set
mysql> desc test ;
+-------------+-------------+------+-----+-------------------+-----------------------------+
| Field       | Type        | Null | Key | Default           | Extra                       |
+-------------+-------------+------+-----+-------------------+-----------------------------+
| id          | int(10)     | NO   | PRI | NULL              | auto_increment              |
| name        | varchar(10) | NO   | MUL | NULL              |                             |
| sex         | char(1)     | YES  |     | NULL              |                             |
| age         | tinyint(3)  | NO   |     | NULL              |                             |
| create_time | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| update_time | date        | NO   |     | NULL              |                             |
+-------------+-------------+------+-----+-------------------+-----------------------------+

• 数值类型字段通过字符串检索时，非数值部分会被当做0处理

mysql> select * from test ;
+----+------+-----+-----+---------------------+-------------+
| id | name | sex | age | create_time         | update_time |
+----+------+-----+-----+---------------------+-------------+
|  1 | 1    | 1   |   1 | 2018-07-29 10:36:35 | 2018-07-29  |
|  2 | 2    | 2   |   2 | 2018-07-29 10:36:48 | 2018-07-29  |
|  3 | 3    | 3   |   3 | 2018-07-29 10:37:01 | 2018-07-29  |
+----+------+-----+-----+---------------------+-------------+
3 rows in set

mysql> select * from test where id = "aaaa" ;
Empty set

mysql> select count(*) from test where id ;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set

• Mysql 一个字段定义成int类型，查询时传入String，会截取字符串
• MySql int型字段where字句时一些问题记录
• MySQL 过滤字段不加条件的执行结果

---

This version of MySQL doesn’t yet support 'LIMIT & IN/ALL/ANY/SOME subquery

select b.* from table_b b where b.id in (
	select a.id from table_a a where id = 394 order by a.id desc limit 0,10 
)
;

• /* SQL错误（1235）：This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’ */
• 子查询中不能带有 in (limit)

---

null和任何值都不能比较

select count(*) from test where status > 3 ;
期待返回非0数值，因为表中存在这样的数据，实际返回0
原因：status tinyint 类型，表结构设计有问题，字段默认为null
表中字段也没有赋值，都为null
而null值不能进行比较，所以上面的SQL返回结果为0

null和任何值都不能比较
null只能用is null 或者is not null 来判断，不能用=或者！=来判断

• MySql数据库中null值与其他值的比较

---

Date_format("%Y-%m-%d") 不同于java中的格式化

• date_format

---

Warning:Truncated incorrect DOUBLE value

update table_a
set a=1 and b=2 

• 语法错误
• 应该为

update table_a
set a=1 , b=2 

---

数据迁移

• 主从数据同步，不允许在从库有长时间的数据查询，会阻塞数据同步
• 先检查是否有查询进程，不是关闭本地的client客户端