Mysql-基础-SQL语句编写规范

建表规范

1. 【强制】表达是与否概念的字段，必须使用 is_xxx的方式命名，数据类型是 unsigned tinyint（1表示是，0表示否）。.
   说明：任何字段如果为非负数，必须是unsigned。
   注意：POJO类中的任何布尔类型的变量，都不要加is前缀，需要在设置从is_xxx到Xxx的映射关系。数据库表示是与否的值，使用tinyint类型，坚持is_xxx的命名方式是为了明确其取值含义与取值范围。
   正例：表达逻辑删除的字段名is_deleted，1表示删除，0表示未删除。

2. 【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。说明：MySQL在Windows下不区分大小写，但在Linux下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任何大写字母，避免节外生枝。

3. 【强制】表名不使用复数名词。 说明:表名应该仅仅表示表里面的实体内容，不应该表示实体数量，对应于 DO 类名也是单数 形式，符合表达习惯。

4. 【强制】任何字段如果为非负数，必须是 unsigned。

5. 【强制】禁用保留字，如 desc、range、match、delayed 等，请参考 MySQL 官方保留字。

6. 【强制】 唯一索引名为 uk_字段名;普通索引名则为 idx_字段名。

说明:uk_ 即 unique key;   idx_ 即 index 的简称。

7. 【强制】 小数类型为 decimal，禁止使用 float 和 double。

说明:float 和 double 在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确的结果。
如果存储的数据范围 超出decimal 的范围，建议将数据拆成整数和小数分开存储。

8. 【强制】如果存储的字符串长度几乎相等，使用 char 定长字符串类型。

9. 【强制】 varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长 度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索 引效率。

10. 【强制】表必备三字段:id, created_date, last_modified_date。 说明:其中id必为主键，类型为unsigned bigint、单表时自增、步长为1。created_date, last_modified_date 的类型均为 date_time 类型。

11. 【推荐】表的命名最好是加上“业务名称_表的作用”。

正例: wms_task / erp_product / mpp_config

12. 【推荐】库名与应用名称尽量一致。

13. 【推荐】如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。

14. 【推荐】 字段允许适当冗余，以提高性能，但是必须考虑数据同步的情况。冗余字段应遵循:

     1)不是频繁修改的字段。

     2)不是 varchar 超长字段，更不能是 text 字段。 正例:商品类目名称使用频率高，字段长度短，名称基本一成不变，
     可在相关联的表中冗余存 储类目名称，避免关联查询。

15. 【推荐】单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。 说明:如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

16. 【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检 索速度。

正例:无符号值可以避免误存负数，且扩大了表示范围。

1.char和varchar之间的区别：

• 最大：char最大的长度为255个字符，varchar最大长度是65535个字符（4.0版本是65535个字节）。
• 存储： char 表示定长，长度固定，varchar表示变长，即长度可变。当所插入的字符串超出它们的长度时，视情况来处理，如果是严格模式，则会拒绝插入并提示错误信息，如果是宽松模式，则会截取然后插入。如果插入的字符串长度小于定义长度时，则会以不同的方式来处理，如char（10），表示存储的是10个字符，无论你插入的是多少，都是10个，如果少于10个，则用空格填满。而varchar（10），小于10个的话，则插入多少个字符就存多少个。
• 取数据:char在取值的时候会把存值后面的空格去除掉，varchar 如果后面有空格则会保留；

使用场景：
 CHAR适合存储很短的字符串，或者所有值都接近同一个长度。例如，CHAR非常适合存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率。例如用CHAR(1)来存储只有Y和N的值，如果采用单字节字符集只需要一个字节，但是VARCHAR(1)却需要两个字节，因为还有一个记录长度的额外字节。

下面这些情况下使用VARCHAR是合适的：字符串很长或者所要存储的字符串长短不一，差别很大；字符串列的最大长度比平均长度大得多；列的更新很少，所以碎片不是问题。

额外说明下，我们在定义字段最大长度时应该按需分配，提前做好预估。特别是对于VARCHAR字段，有人认为反正VARCHAR数据类型是根据实际的需要来分配长度的，还不如给大一点呢。但事实不是这样的，比如现在需要存储一个地址信息，根据评估，只要使用100个字符就可以了，我们可以使用VARCHAR(100)或VARCHAR(200)来存储，虽然它们用来存储90个字符的数据，其存储空间相同，但是对于内存的消耗是不同的。更长的列会消耗更多的内存，因为MySQL通常会分配固定大小的内存块来保存内部值，尤其是使用内存临时表进行排列或者操作时会特别糟糕。所以我们在分配VARCHAR数据类型时仍然不能够太过于慷慨。还是要评估实际需要的长度，然后选择一个最长的字段来设置字符长度。如果为了考虑冗余，可以留10%左右的字符长度。千万不能认为VARCHAR是根据实际长度来分配存储空间，而随意的分配长度，或者说干脆使用最大的字符长度。

2.日期的比较

参考博文1.点击这里
1.查询具体的日期：
假如有个表product有个字段add_time,它的数据类型为datetime,有人可能会这样写sql：

select * from product where add_time = '2013-01-12'

对于这种语句，如果你存储的格式是yyyy-MM-dd是这样的，那么OK。如果你存储的格式是：yyyy-MM-dd HH:mm:ss这种格式就悲剧了，这时你就可以使用DATE()函数用来返回日期的部分，所以这条sql应该如下处理：

select * from product where Date(add_time) = '2013-01-12'

再来一个，如果你要查询2013年1月份加入的产品呢？

select * from product where date(add_time) between '2013-01-01' and '2013-01-31'

–你还可以这样写：

select * from product where Year(add_time) = 2013 and Month(add_time) = 1

DATE_DIFF(date1,date2)：

两个日期相减，date1 与date2 都可以是单独的日期 或日期与时间，但只有日期部分参与运算。
6、DATE_FORMAT(date,format)：

DATE_FORMAT(date,format)：

用format 格式化date，format 为格式化字符串，常用的部分格式化标识符为：

两个时间比较：

同样，可通过>或<比较大小，也可通过时间相减函数subtime(time1,time2)。
注意：在Mybatis的书写过程中使用转义字符<表示小于，>表示大于。
参考文章，点击这里

自己的理解：

一般在Mybatis的书写过程中：
1.如果两个比较的时间类型都是Date，则直接使用&lt；或者&gt；进行比较即可。

## 其中：dayStart，endStart都是Date类型，并且数据库中存储的create_time也是Date类型。
 select count(1) from sys_log where log_type = 1 and create_time >= #{dayStart} and create_time < #{dayEnd}

2.情况2：前端传值进来和数据库中存储的时间进行比对的时候，为了保证格式的统一方便比值，需要进行转换。

	<if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
				and date_format(create_time,'%y%m%d') &gt;= date_format(#{params.beginTime},'%y%m%d')
			</if>
			<if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
				and date_format(create_time,'%y%m%d') &lt;= date_format(#{params.endTime},'%y%m%d')
			</if>

注意：，前端比如Swagger传值的时候不用使用“”，直接写2021-04-16，并且在类进行接收的时候前DateFormat的格式和sql语句的date_format格式必须一致。

1.SQL 查询表的第一条数据 和 最后一条数据

方法一: 使用TOP

SELECT TOP 1 * FROM user;
 
SELECT TOP 1 * FROM user order by id desc;

方法二：使用Limit

SELECT * FROM user LIMIT 1;
 
SELECT * FROM user ORDER BY id ASC LIMIT 1;
 
SELECT * FROM user ORDER BY id DESC LIMIT 1;

2.Group by的使用注意事项

需要进行聚合函数的时候，就需要进行分组

• group by有一个原则，就是select后面所有的列中，没有使用聚合函数的列，必须出现在group by子句中。
• 在GROUP BY子句中不能使用SELECT子句中定义的别名；

SELECT col_a/(*)/(聚合函数) FROM tb WHERE 条件 
GROUP BY col_a HAVING COUNT(*)>5 ORDER BY sum DESC
LIMIT 10;

where、groupby，having，orderby的顺序

3.SQL中的排名函数

参考博文

4.count()和sum()之间的区别