MySQL常见问题解答要点
在主键中设置常规规则在sql/中不能使用distinct '关键字/为某些SQL客户端编写存储过程，您需要使用DELIMITER命令来设置/alter table的terminator/common用法/like '1000 '和regexp '1000 '之间的区别/EXPLAIN命令来查看执行计划/
MySQL基础常见问题集锦
在sql主键中设置常规规则
不要更新主键列的值(无法更新要设置主键的列)
不要重复使用主键列的值(主键值在整个主键列中必须是唯一的)
不要使用可能在主键列中更改的值(主键列不能与其他列有绑定更新关系)
不能使用Partial distinct关键字。
Distinct关键字适用于所有列，而不仅仅是它前面的列。
(distinct关键字以粒度区分查询结果的行，如果一行数据的显示方式不同，则如果相同，则只显示一行数据)
mysql SELECT DISTINCT student_id，student_name，student _生日FROM student
- - -
|学生_id |学生_姓名|学生_生日|
- - -
| 1 | java | 2021-11-01 |
| 2 | python | 2021-11-01 |
| 3 | c | 2021-11-01 |
| 4 | java | 2021-11-02 |
| 5 | java | 2021-11-02 |
- - -
5行一组(0.00秒)
mysql SELECT DISTINCT学生_姓名，学生_生日FROM学生；
- -
|学生名|学生生日|
- -
| java | 2021-11-01 |
| python | 2021-11-01 |
| c | 2021-11-01 |
| java | 2021-11-02 |
- -
4行一组(0.00秒)
限制1，1实际表示
Limit 1，1表示从第2行开始，显示第1行，Limit检索第一行的第0行。
Mysql5.0之后，limit 4 offset 3表示：表示从第三行开始，显示四行。
存在和不存在比在和不在更有效率。
比如“1000”和正则表达式“1000”
就像“1000”匹配整个列一样，即使该列包含1000，它也不会返回结果。
当regexp '1000 '匹配时，它匹配整个列。如果列包含1000，将返回结果。
大多数DBMS使用拼接或||，MySQL使用Concat(A，B，C.)功能。
DATEDIFF(NOW()，' 1995-12-01 ')函数可以计算指定日期和现在之间的天数。
>STR_TO_DATE('1995,12,01','%Y,%m,%d') 函数可将指定字符串转成日期格式
count( 1 )表示在查询结果的基础上为每行新增一列 1 的填充列 , 计算 增加1的个数达到统计效果
group by 使用时除聚合计算以外, select语句中每个列都必须在group by子句中给出
where 用于分组后组外过滤 , having用于分组时组内过滤
union 用于组合相同的返回列, 并去除组合后重复值 , union all 组合后不去除重复值
MyISAM引擎支持全文搜索(仅作了解)
-
代码
-- 建表 CREATE TABLE class( class_id INT, class_name VARCHAR(20), PRIMARY KEY (class_id), FULLTEXT(class_name) -- 指定全文搜索需要建立的索引列 )ENGINE=MYISAM; --使用全文搜索 SELECT class_name FROM class WHERE MATCH(class_name) AGAINST('p1');
insert into中间可添加关键字LOW_PRIORITY以降低插入数据优先级, 保证select优先级
insert select 中语句不要求返回列名一致 , 但要求返回查询结果返回顺序与插入一致
update语句更新多行时, 如果一行出现错误可导致整体更新失败 , update ignore tablename 中ignore可跳过失败继续更新
alter table 的常用用法
-
给表添加一列
ALTER TABLE student ADD remark VARCHAR(20); -
给表修改列名
ALTER TABLE student CHANGE remark intro varchar(30); -
删除列
ALTER TABLE student DROP intro; -
修改列类型
ALTER TABLE student MODIFY remark VARCHAR(100);
SQL客户端写存储过程时, 需要用 DELIMITER 命令设置结束符
DELIMITER // --设置操作符
CREATE PROCEDURE query_student_info_arg(
OUT student_id INT,
OUT class_id INT,
OUT school_id INT
)
BEGIN
SELECT MAX(student.`student_id`) INTO student_id FROM student ;
SELECT MAX(class.`class_id`) INTO class_id FROM class ;
SELECT MAX(school.`school_id`) INTO school_id FROM school ;
END//
DELIMITER ; -- 在将操作符设置回 ;
使用EXPLAIN命令查看执行计划时 , type 列执行效率依次从最优到最差分别为:
system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL
使用EXPLAIN命令查看执行计划时 , Extra列重要值及优化方案分别为:
distinct: 一旦mysql找到了与行相联合匹配的行，就不再搜索了
Using index：这发生在对表的请求列都是同一索引的部分的时候，返回的列数据只使用了索引中的信息，而没有再去访问表中的行记录。是性能高的表现。
Using where：mysql服务器将在存储引擎检索行后再进行过滤。就是先读取整行数据，再按 where 条件进行检查，符合就留下，不符合就丢弃。
Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。
Using filesort：mysql 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。
阿里编码规约中关于MySQL数据库部分
建表规约
- 【强制】 表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint
（ 1 表示是， 0 表示否）。 - 【强制】 表名、字段名必须使用小写字母或数字， 禁止出现数字开头，禁止两个下划线中间只
出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。 - 【强制】 表名不使用复数名词。
- 【强制】 禁用保留字，如 desc、 range、 match、 delayed 等， 请参考 MySQL 官方保留字。
- 【强制】 主键索引名为 pk_字段名；唯一索引名为 uk_字段名； 普通索引名则为 idx_字段名。
- 【强制】 小数类型为 decimal，禁止使用 float 和 double。
- 【强制】 如果存储的字符串长度几乎相等，使用 char 定长字符串类型。
- 【强制】 varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度
大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效
率。 - 【强制】 表必备三字段： id, gmt_create, gmt_modified。
- 【推荐】 表的命名最好是遵循“业务名称_表的作用” 。
- 【推荐】 库名与应用名称尽量一致。
- 【推荐】 如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。
- 【推荐】 字段允许适当冗余，以提高查询性能，但必须考虑数据一致。冗余字段应遵循：
1） 不是频繁修改的字段。
2） 不是唯一索引的字段。
3） 不是 varchar 超长字段，更不能是 text 字段。 - 【推荐】 单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。
- 【参考】 合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索
速度。
索引规约
- 【强制】 业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。
- 【强制】 超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致； 多表关联查询时，
保证被关联的字段需要有索引 - 【强制】 在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据
实际文本区分度决定索引长度。 - 【强制】 页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
- 【推荐】 如果有 order by 的场景，请注意利用索引的有序性。 order by 最后的字段是组合索
引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。 - 【推荐】 利用覆盖索引来进行查询操作， 避免回表。
- 【推荐】 利用延迟关联或者子查询优化超多分页场景。
- 【推荐】 SQL 性能优化的目标：至少要达到 range 级别， 要求是 ref 级别， 如果可以是 consts最好。
- 【推荐】 建组合索引的时候，区分度最高的在最左边。
- 【推荐】 防止因字段类型不同造成的隐式转换， 导致索引失效。
文章部分笔记来源 : 《MySQL必知必会》by Ben Forta 、《Java开发手册(泰山版)》by Alibaba
文章博客其他地址 :
[STR_TO_DATE 函数转换日期格式说明]: https://www.cnblogs.com/feiwenstyle/p/9531571.html "STR_TO_DATE 函数转换日期格式说明"
[mysql explain命令说明及优化方案]: https://cloud.tencent.com/developer/article/1093229 "mysql explain详解"
内容来源网络，如有侵权，联系删除，本文地址：https://www.230890.com/zhan/112520.html