添加链接 注册    登录
link之家
链接快照平台
  • 输入网页链接，自动生成快照
  • 标签化管理网页链接
相关文章推荐
忐忑的土豆  ·  一直报错“falat ...·  2 月前    · 
豪爽的钱包  ·  舒红兵院士获得大奖，不受小14岁老婆王延轶影 ...·  4 月前    · 
胆小的打火机  ·  臺北醫學大學 人體研究處 - 業務職掌·  1 年前    · 
直爽的围巾  ·  描写时间飞逝的作文开头有哪些-百度经验·  1 年前    · 
仗义的花生  ·  鬼谷黑名单-爱的比热容·  1 年前    · 
link之家  ›  【小家SQL】MySql数据类型---日期时间类型的使用（含datetime和timestamp的区别） 0000-00-00 00:00:00问题解释__mysql表示日期的数据类型
mysql 时间戳 mysql时间函数 mysql日期函数
https://blog.51cto.com/u_3631118/3121356
犯傻的海豚
1 年前

在这一路学习过来，每次不管看书还是网上看的资料，对于MySQL数据类型中的时间日期类型总是一扫而过，不曾停下来认认真真的研究学习。最近看了一本关于MySql的书籍，打算全面的学习研究一遍。

虽然每次都没有怎么注意，但是使用起来确实非常的糟糕。每次都转换起来非常不便。比如明明我只需要日期，却给我一个时间戳或者时分秒都有的东西之类的。

在之前，我对于时间日期数据类型不怎么感冒，也没怎么用过这一类型。在我的做项目里用到存贮时间的数据，我都是采用int整型数据类型来存储，即是存储时间戳。但是在后面学习MySQL优化的时候，就有一个原则就是存储数据时应采用最小占用空间的数据类型。int类型是4个字节，TIMESTAMP也是4个字节，但是在需要使用日期时，时间戳还需要进一步转换，而TIMESTAMP类型数据就不需要了。

时间日期数据类型总概况

MySQL中有多种表示时间日期的数据类型，主要有YEAR、TIME、DATE、DATETIME、TIMESTAMP等。每一种数据类型都有存储的时间日期格式、以及取值范围，因此在使用时间日期数据类型的时候需要选取最佳的数据类型。
【小家SQL】MySql数据类型---日期时间类型的使用（含datetime和timestamp的区别） 0000-00-00 00:00:00问题解释_享学MySQL

此处注意发现：Time类型HH的取值竟然是800+，而不是我们本以为的24以内的数字，具体原因，下面会有解释

各类型详细讲解

1、Year

见名之意，year用于存储年，存储时只需要 一个字节 ，插入数据时可以使用各种格式指定YEAR值（非常节约内存，所以当你只需要年的时候，用此字段合适）。

支持的常见插入格式为：

  1. （推荐甚至强制要求必须） 四位 字符串 或者 数字 ，范围为“1901”~“2155”，写多少即为多少
  2. 两位字符串格式：范围为“00” “99”，"00" "69"被转化为20**（例如：“16”转化为“2016”），“70”~“99”被转化为19**（下同）
  3. 两位数字格式，范围为1 99，1 69被转化为20**（例如：1转化为2001），70~99被转化为19**
2、Time

time用于存储时间信息，存储时需要三个字节。
虽然，小时的范围是0~~23，但是为了表示某种特殊需要的 时间间隔 ，将Time类型的范围扩大了。而且还支持了负值。

支持的常见插入格式为：

  1. （推荐甚至强制要求必须） 字符串格式：‘HH:MM:SS’,‘HH:MM’,‘D HH:MM’,‘D HH’,'SS’等形式。举个例子，输入‘30’，Time类型会自动转换为00：00：30。
  2. ‘HHMMSS’格式的字符串或HHMMSS格式的数值表示，例如，输入‘123456’，Time类型会转换成12：34：56；输入123456，Time类型会转换成12：34：56。如果输入0或者‘0’，那么TIME类型会转换为0000：00：00。
  3. ‘D HH:MM:SS’格式的字符串表示。其中，D表示天数，取值范围是0~~34。保存时，小时的值等于（D*24+HH）。举个例子，输入‘2 11：30：50’，Time类型会转换为59：30：50。
  4. 使用current_time或者current_time()或者now()输入当前系统时间。（一般用于默认值）

SQL示例： 

INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('1', '1 01:50:50');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('2', '01:50:50');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('3', '50:05');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('4', '1 05:05');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('5', '59');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('6', '66');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('7', '123456');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('8', 123456);
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('9', 0);
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('10', '0');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('11', now());
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('12', current_time);
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('13', current_time());

Time类型专门用来存储时间数据，而且只占3个字节，所以如果只需要记录时间，选择Time类型是最合适的。


3、Date


date用于存储日期，没有时间部分，存储时需要三个字节。


MySQL中是以YYYY-MM-DD的形式显示date类型的值。


支持的常见插入格式为：


  • （推荐甚至强制要求必须） ‘YYYY-MM-DD’或‘YYYYMMDD’格式的字符串表示，这种方式可以表达的范围是‘1000-01-01’~‘9999-12-31’。
    • 

  • MySQL中还支持一些不严格的语法格式，任何标点都可以用来做间隔符。如’YYYY/MM/DD‘，’YYYY@MM@DD‘，’YYYY.MM.DD‘等分隔形式。举个例子，输入’2011.3.8‘，date类型将转换为2011-03-08。
    • 

  • ’YY-MM-DD‘或者’YYMMDD‘格式的字符串表示，其中’YY‘的取值，’00‘’69‘转换为20002069，’70‘’99‘转换为19701999。与year类型类似。
    • 

  • 使用current_date或now()来输入当前系统时间。
    • 

SQL示例：


INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('1', '2008-08-08');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('2', '20080808');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('3', '2008@08@08');
-- 格式出错，所以插入0000-00-00
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('4', '2008#0808');
-- 格式没错，但是小于了date类型的最小值1000，但是数据库还是插进去了，我晕
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('5', '0999-08-08');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('6', '690808');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('7', '700808');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('8', 690808);
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('9', 700808);
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('10', '0');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('11', 0);
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('12', now());
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('13', current_date);
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('14', current_date());

4、Datetime


datetime类型使用8个字节来表示日期和时间。
MySQL中以‘YYYY-MM-DD HH:MM:SS’的形式来显示dateTime类型的值。


支持的常见插入格式为：


  • （推荐甚至强制要求必须）‘YYYY-MM-DD HH:MM:SS’或‘YYYYMMDDHHMMSS’格式的字符串表示。这种方式可以表达的范围是‘1000-01-01 00:00:00’~~‘9999-12-31 23:59:59’。
    • 

  • MySQL中还支持一些不严格的语法格式，任何的标点都可以用来做间隔符。情况与date类型相同，而且时间部分也可以使用任意的分隔符隔开，这与Time类型不同，Time类型只能用‘:’隔开呢。
    • 

  • 使用now()来输入当前系统日期和时间。
    • 

SQL示例：


INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('1', '2008-08-08 08:08:08');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('2', '20080808080808');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('3', '2008@08@08 08*08*08');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('4', '69-01-01 11:11:11');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('5', '70-01-01 11:11:11');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('6', 20080808080808);
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('7', 690808080808);
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('8', 700808080808);
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('9', 0);
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('10', now());

dateTime类型用来记录日期和时间，其作用等价于date类型和Time类型的组合。一个dateTime类型的字段可以用一个date类型的字段和一个time类型的字段代替。但是如果需要同时记录日期和时间，选择dateTime类型是个不错的选择。


5、timestamp


timestamp类型使用4个字节来表示日期和时间。
timestamp类型与dateTime类型显示的格式是一样的。


支持的常见插入格式为：
同datetime


二者主要区别在于取值范围。


  • timestamp存储需要四个字节，它的取值范围为“1970-01-01 00:00:01” UTC ~ “2038-01-19 03:14:07” （和时区有关）
    • 

  • 而datetime取值范围为“1000-01-01 00:00:00” ~ “9999-12-31 23:59:59”（和时区无关，怎么存入怎么返回，对程序员友好）
    • 

SQL示例：同datetime（但使用的是current_timestamp和now()）


INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('1', null);
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('2', 'NULL');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('3', current_timestamp());

从数据库显示的结果来看，timestamp的范围确实很小的，2069明显的超过了2038，所以数据库插入0。


在MySQL 5.6.5版本之前，Automatic Initialization and Updating只适用于TIMESTAMP，而且一张表中，最多允许一个TIMESTAMP字段采用该特性。从MySQL 5.6.5开始，Automatic Initialization and Updating同时适用于TIMESTAMP和DATETIME，且不限制数量。


datetime和timestamp的比较


1、timestamp相对于datetime的不同之处：
（1.1），使用current_timestamp来输入系统当前日期与时间
（1.2），输入null时，系统会输入系统当前日期与时间
（1.3），无任何输入时，系统会输入null。


资料上面说系统会输入系统当前日期与时间，但是我自己尝试了下，如果输入null的时候，数据库中也是null，郁闷。 估摸和MySql版本有关


2、timestamp类型还有一个很大的特殊点，就是时间是根据时区来显示的。
例如，在东八区插入的timestamp类型为2009-09-30 14:21:25，在东七区显示时，时间部门就变成了13:21:25，在东九区显示时，时间部门就变成了15:21:25。
3、需要显示日期与时间，timestamp类型需要根据不同地区的时区来转换时间，但是，timestamp类型的范围太小，其最大时间为2038-01-19 11:14:07。
如果插入时间的比这个大，将会数据库插入0000-00-00 00:00:00。所以需要的时间范围比较大，还是选择dateTime类型比较安全。


MySQL中如何表示当前时间？


其实，表达方式还是蛮多的，汇总如下：
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP()
NOW()
LOCALTIME
LOCALTIME()
LOCALTIMESTAMP
LOCALTIMESTAMP()


了解MySQL的日期时间数据类型对于选取一种适合存储类型是很有必要的。假若只有存储年份可以选取YEAR、仅存储时间可以选择TIME、又或者需要存储完整日期时间，那么可以根据实际情况选取DATATIME（推荐）或者TIMESTAMP数据类型。


附：MySql常用转换函数


unix_timestamp(),
unix_timestamp(date),
from_unixtime(unix_timestamp),
from_unixtime(unix_timestamp,format)


select unix_timestamp(); -- 1218290027
select unix_timestamp('2008-08-08'); -- 1218124800
select unix_timestamp('2008-08-08 12:30:00'); -- 1218169800
select from_unixtime(1218290027); -- '2008-08-09 21:53:47'
select from_unixtime(1218124800); -- '2008-08-08 00:00:00'
select from_unixtime(1218169800); -- '2008-08-08 12:30:00'
select from_unixtime(1218169800, '%Y %D %M %h:%i:%s %x'); -- '2008 8th August 12:30:00 2008'

date_format(date,format), time_format(time,format) 能够把一个日期/时间转换成各种各样的字符串格式。它是 str_to_date(str,format) 函数的 一个逆转换。


附：MySql各大数据类型占用字节数


修正：varchar最大大小是65532字节。char是定长(每个值都占用M个字节，如果某个长度小于M，MySQL就会在它的右边用空格字符补足)，varchar是变长


MySQL中int(M)和tinyint(M)数值类型中M值的意义


附：mysql中的date类型直接比较大小是按照字符串比较还是时间戳


Mysql在比较两种不同数据类型时，第一步是将他们转化为同一种类型，然后在比较。那么Date和String在比较的时候，一定是把String转化为Date吗？答案是Yes.


select * FROM test.orders where ceate_record_time > '2019'

结果截图：
【小家SQL】MySql数据类型---日期时间类型的使用（含datetime和timestamp的区别） 0000-00-00 00:00:00问题解释_datetime_06
为什么会出现 2018 的字符串？


再举个例子：如果是用字符串比较，“2004-04-31"这个string应该比2004-01-01这个date来得大，但是4-31是一个invalid的日期（4月是小月），会被转化成"0000-00-00”，所以2004-01-01 (日期) > “2014-04-31”。


所以，在存储方面：如果你是表示的时间，请尽量不要采用str类型来存储（虽然大多数情况下存入的效果一样，但不建议）。
在查询方面：如果你确实遇到的存储的是字符串，那么请用STR_TO_DATE函数转成日期格式在查询，形如：


select * from orders where date(str_to_date(`ceate_record_time`.`publish_date`,'%Y-%m-%d')) > '2019-0-0'

附：unsigned解释


整型的每一种都分有无符号（unsigned）和有符号（signed）两种类型（float和double总是带符号的），在默认情况下声明的整型变量都是有符号的类型。


如果需声明无符号类型的话就需要在类型前加上unsigned。无符号版本和有符号版本的区别就是无符号类型能保存2倍于有符号类型的正整数数据，比如16位系统中一个int能存储的数据的范围为-3276832767，而unsigned能存储的数据范围则是065535。由于在计算机中,整数是以补码形式存放的。根据最高位的不同，如果是1,有符号数的话就是负数；如果是无符号数,则都解释为正数。


简而言之就是由于Int型占4字节，也就是16位，2^16 = 65535，如果有符号位就+ -两边均分，如果没有就全给+


因此下面建表语句是被推荐的：


CREATE TABLE `fast_group_task` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='批量组建班级的任务';

Java中的 '0000-00-00 00:00:00’问题


在我们的数据库定义中，经常可以看到类似这样的定义：


`start_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'

这样定义的隐患其实是比较大的。如果我们链接Mysql的Url参数上不作为，那么查询就会报错如下：


java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp

通过此报错我们其实应该很敏感的得出两个结论：


  1. MySql是允许0000-00-00 00:00:00这样的值存在的（否则就会报SQL执行的错，而不是现在的封装成java.sql.Timestamp的错）
    2. 

  2. Java中（至少java.sql.Timestamp这个类）是不能支持到0000-00-00 00:00:00这个时间的。
    3. 

// 这句语句报错，也能说明我们的Timestamp根本就不能表示这个时间~~~ 
// 最主要的是，这种时间没有任何实际意义，就连MySql存储它都采用的是假时间来存储的，强烈不建议使用
System.out.println(Timestamp.valueOf("0000-00-00 00:00:00")); 

我们的解决方案有两种：
1、jdbc的url后面，追加上参数：?zeroDateTimeBehavior=convertToNull，这样子Sql查出来的结果是null，Java进行封装就没有问题了
2、修改timestamp字段的默认值，不再采用0000-00-00 00:00:00（一般用当前时间）


总的来说，不要给数据库字段默认值设置为0000-00-00 00:00:00，完全是给自己找麻烦。
另外，JDK8已经完全普及了，强烈建议不再使用Date和Timestamp来表示时间，而使用JSR310的新规范，比老的更好用且功能更强大

                            
java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Tim

                            
 分析：loginTime的时间有误。

                            
【Java异常】java.sql.SQLExcetion:Cannot convert value “0000-00-00 00:00:00” from column 9 to TIMESTAMP

                            
 本文目录一、背景描述二、错误原因三、解决方案方案1、修改配置文件的数据源url方案2、直接修改数据库一、背景描述Spring bo

                            
Caused by: java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Tim

                            
 1、错误描述### Cause: java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Tim...

                            
java 去重 java去重排序

                            
 1.去重1.1 for循环去重for (int i = 0; i<list.size();i++) {
            for (int j=i+1;j<list.size();j++) {
                if (list.get(i).equals(list.get(j))) {
                    list.remove(j);
                                    00e0-fc09-bcf9
                                    01 03 00 04 00 02 85 CA
                                    Data truncation: Incorrect datetime value: '0000-00-00 00:00:00' for column
                                    Incorrect datetime value: '0000-00-00 00:00:00' for column
                                    Incorrect datetime value: '0000-00-00' for column 'login_time' at row 1
                                    MSR 26-00
                                    bnxtroce.v00
                                    elxnet.v00
                                    error loading /tboot.b00
 
推荐文章
忐忑的土豆  ·  一直报错“falat error”该怎么办【植物大战僵尸吧】_百度贴吧
2 月前
豪爽的钱包  ·  舒红兵院士获得大奖，不受小14岁老婆王延轶影响，他是个好丈夫|研究所|科学家|医学家|武汉大学|生物学家_网易订阅
4 月前
胆小的打火机  ·  臺北醫學大學 人體研究處 - 業務職掌
1 年前
直爽的围巾  ·  描写时间飞逝的作文开头有哪些-百度经验
1 年前
仗义的花生  ·  鬼谷黑名单-爱的比热容
1 年前
今天看啥   ·   Py中国   ·   codingpro   ·   藏经阁   ·   小百科   ·   link之家   ·   卧龙AI搜索
删除内容请联系邮箱 2879853325@qq.com
link之家 - 链接快照平台
© 2024 ~ 沪ICP备11025650号