MySQL踩坑

2021-04-13

关于数据库时区说明

2016-12-07T03:13:25.808Z:T是代表后面跟着“时间”。Z代表0时区，
这是UTC统一时间

时间的描述：

mysql数据库拥有时区设置，默认使用系统时区

可通过如下语句查询当前时区

show variables like '%time_zone%';

数据库使用系统时间CST——China Standard Time UTC+8:00 中国沿海时间(北京时间)

UTC:国际时间

UTC-8:伦敦时间

UTC+8就是国际时加八小时,是东八区时间,也就是北京时间

GMT，即格林尼治标准时间，也就是世界时。GMT的正午是指当太阳横穿格林尼治子午线（本初子午线）时的时间。但由于地球自转不均匀不规则，导致GMT不精确，现在已经不再作为世界标准时间使用。

UTC，即协调世界时。UTC是以原子时秒长为基础，在时刻上尽量接近于GMT的一种时间计量系统。为确保UTC与GMT相差不会超过0.9秒，在有需要的情况下会在UTC内加上正或负闰秒。UTC现在作为世界标准时间使用。

所以，UTC与GMT基本上等同，误差不超过0.9秒

RTC，硬件时间，主板上面bios的时间

String dateTime = "2019-06-27T16:00:00.000Z";

dateTime = dateTime .replace("Z", " UTC"); //2019-06-27T16:00:00.000 UTC

SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS Z");//转换时区格式
SimpleDateFormat format2 = new SimpleDateFormat("yyyy-MM-dd");

Date date = format1.parse(dateTime ) || long ldate = format1.parse(dateTime ).getTime();//将Z时间格式转换成Date类型格式或换成毫秒
String time= format2.format(date);//2019-06-28

System.out.println(time);

输出：2019-06-28

时间类型说明

datetime

实际格式储存（Just stores what you have stored and retrieves the same thing which you have stored.）

与时区无关（It has nothing to deal with the TIMEZONE and Conversion.）

timestamp

值以UTC毫秒数保存（ it stores the number of milliseconds）

存储及检索时根据当前时区设置，对时间数值做转换

由于timestamp与时区相关，且线上数据库时区设置为北京时间（即UTC+8:00）。因此，当数据库中使用了timestamp列，若使用不当，统一UTC格式时间改造将很可能会引入错误！

关于时间查询的sql语句

在项目开发过程中，自己需要查询出一定时间段内的交易。故需要在sql查询语句中加入日期时间要素，sql语句如何实现?

1	SELECT * FROM lmapp.lm_bill where tx_time Between '2015-12-20' And '2015-12-31';

查询每一天某个时间段的所有记录(例:9:00:00到21:00:00)

SELECT * FROM 表名 
WHERE DATE_FORMAT(create_time,'%H:%i:%S')>='09:00:00'
and DATE_FORMAT(create_time,'%H:%i:%S')<='21:00:00' 
ORDER BY 排序字段 ASC;

mysql 查询当天、最近一天、最近一周，本月，上一个月的数据

今天
select * from 表名 where to_days(时间字段名) = to_days(now());

昨天

SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1
7天

1	SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)

近30天

1	SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)

本月

1	SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

上一月

1	SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1

SELECT id FROM m_question WHERE cratedate BETWEEN
‘2018-05-31 12:00:00’ and ‘2018-06-26 14:59:59’ ORDER BY id DESC
或者
SELECT id FROM m_question WHERE cratedate >
‘2018-05-31 12:00:00’ and cratedate < ‘2018-06-26 14:59:59’ ORDER BY id DESC
但是between and 包含时间界限，也相当于>= <=，而> <不包含

Select  *  from mytable where  DateField1<='2010-1-31'

Select  *  from mytable where  DateField1<='2010-1-31 0:0:0'

Select  *  from mytable where  DateField1< '2010-2-1'

Select  *  from mytable where  DateField1<'2010-2-1 0:0:0'

如果DateField1 里面有值为 2010-1-31 ，我们不建议用语句1 或者语句 2，因为由于数据库的数据精度和编程语言的数据精度不一致，

使用<= 会导致无法取得 2010-1-31 的数据

关于数据库enum类型

避免使用ENUM数据类型（修改ENUM值需要使用ALTER语句，ENUM类型的ORDER BY操作效率低，需要额外操作，禁止使用书值作为ENUM的枚举值

不要拿mysql的enum类型取存一些数字；如果你一定要使用这个字段去存数字，请把这个字段定义为int，然后在java代码中使用枚举类做一个对于这个字段值范围的一个限定

关于数据库字符集utf8和utf8mb4的区别

MySQL 的“utf8mb4”是真正的“UTF-8”

MySQL 的“utf8”是一种“专属的编码”，它能够编码的 Unicode 字符并不多

MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode

好在utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换。当然，为了节省空间，一般情况下使用utf8也就够了

为了获取更好的兼容性，应该总是使用 utf8mb4 而非 utf8. 对于 CHAR 类型数据，utf8mb4 会多消耗一些空间，根据 Mysql 官方建议，使用 VARCHAR 替代 CHAR。