MySQL_02 数据类型

1. 数据类型
1.1 数值类型
类型             大小                                           范围（有符号）                                              范围（无符号）
TINYINT            1 Bytes                                       (-128,127)                                                 (0，255)
SMALLINT        2 Bytes                                       (-32768，32767)                                            (0，65535)
MEDIUMINT        3 Bytes                                       (-8388608，8388607)                                        (0，16777215)
INT或INTEGER    4 Bytes                                       (-2 147 483 648，2 147 483 647)                            (0，4 294 967 295)
BIGINT            8 Bytes                                       (-9 233 372 036 854 775 808，9 223 372 036 854 775 807)    (0，18 446 744 073 709 551 615)
FLOAT            4 Bytes
DOUBLE            8 Bytes
DECIMAL            对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2

1.2 日期和时间类型
类型              大小(bytes)  范围                                                格式                      用途
DATE              3              1000-01-01/9999-12-31                               YYYY-MM-DD                 日期值
TIME              3              '-838:59:59'/'838:59:59'                         HH:MM:SS                     时间值或持续时间
YEAR                 1              1901/2155                                           YYYY                         年份值
DATETIME          8              1000-01-01 00:00:00/9999-12-31 23:59:59           YYYY-MM-DD HH:MM:SS         混合日期和时间值
TIMESTAMP          4              1970-01-01 00:00:00/2038                         YYYYMMDD HHMMSS             混合日期和时间值，时间戳


1.3 字符串类型
类型            大小                      用途
CHAR            0-255 bytes                 定长字符串
VARCHAR            0-65535 bytes             变长字符串
TINYBLOB        0-255 bytes                 不超过 255 个字符的二进制字符串
TINYTEXT        0-255 bytes                 短文本字符串
BLOB            0-65 535 bytes             二进制形式的长文本数据
TEXT            0-65 535 bytes             长文本数据
MEDIUMBLOB        0-16 777 215 bytes         二进制形式的中等长度文本数据
MEDIUMTEXT        0-16 777 215 bytes         中等长度文本数据
LONGBLOB        0-4 294 967 295 bytes    二进制形式的极大文本数据
LONGTEXT        0-4 294 967 295 bytes    极大文本数据
注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

2. 整数类型
2.1 有符号类型
MariaDB [web1]> CREATE TABLE demo1(
    -> c1 tinyint
    -> );

MariaDB [web1]> INSERT INTO demo1 VALUES(-pow(2,7)),(pow(2,7)-1);
MariaDB [web1]> SELECT * FROM demo1;
+------+
| c1   |
+------+
| -128 |
|  127 |
+------+

MariaDB [web1]> DESC demo1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c1    | tinyint(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

MariaDB [web1]> INSERT INTO demo1 VALUES(pow(2,7));
ERROR 1264 (22003): Out of range value for column 'c1' at row 1

2.2 无符号整型
MariaDB [web1]> CREATE TABLE demo2(
    -> c1 tinyint unsigned
    -> );

MariaDB [web1]> INSERT INTO demo2 VALUES(-1);
ERROR 1264 (22003): Out of range value for column 'c1' at row 1

MariaDB [web1]> INSERT INTO demo2 VALUES(0),(pow(2,8)-1);
MariaDB [web1]> SELECT * FROM demo2;
+------+
| c1   |
+------+
|    0 |
|  255 |
+------+

2.3 类型n说明
示例1：
N表⽰的是显⽰宽度，不⾜的⽤0补⾜，超过的⽆视长度⽽直接显⽰整个数字，但这要整型设置了unsigned,zerofill才有效
 CREATE TABLE demo3 (
 `a` int,
 `b` int(5),
 `c` int(5) unsigned,
 `d` int(5) zerofill,
 `e` int(5) unsigned zerofill,
 `f` int zerofill,
 `g` int unsigned zerofill
 );

MariaDB [web1]> INSERT INTO demo3 VALUES(1,1,1,1,1,1,1), (11,11,11,11,11,11,11),(12345,12345,12345,12345,12345,12345,12345);
MariaDB [web1]> DESC demo3;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| a     | int(11)                   | YES  |     | NULL    |       |
| b     | int(5)                    | YES  |     | NULL    |       |
| c     | int(5) unsigned           | YES  |     | NULL    |       |
| d     | int(5) unsigned zerofill  | YES  |     | NULL    |       |
| e     | int(5) unsigned zerofill  | YES  |     | NULL    |       |
| f     | int(10) unsigned zerofill | YES  |     | NULL    |       |
| g     | int(10) unsigned zerofill | YES  |     | NULL    |       |
+-------+---------------------------+------+-----+---------+-------+

MariaDB [web1]> SELECT * FROM demo3;
+-------+-------+-------+-------+-------+------------+------------+
| a     | b     | c     | d     | e     | f          | g          |
+-------+-------+-------+-------+-------+------------+------------+
|     1 |     1 |     1 | 00001 | 00001 | 0000000001 | 0000000001 |
|    11 |    11 |    11 | 00011 | 00011 | 0000000011 | 0000000011 |
| 12345 | 12345 | 12345 | 12345 | 12345 | 0000012345 | 0000012345 |
+-------+-------+-------+-------+-------+------------+------------+

示例2：
bigint⽆符号最⼤值为 2^64 -1 = 18,446,744,073,709,551,615；长度是20位，来个bigint左边0填充的⽰例看⼀下
MariaDB [web1]> CREATE TABLE demo4(
    -> `a` bigint zerofill
    -> );

MariaDB [web1]> INSERT INTO demo4 VALUES(1);
MariaDB [web1]> SELECT * FROM demo4;
+----------------------+
| a                    |
+----------------------+
| 00000000000000000001 |
+----------------------+

2.4 浮点类型
示例1：
MariaDB [web1]> CREATE TABLE demo5(a float(5,2),b double(5,2),c decimal(5,2));
MariaDB [web1]> INSERT INTO demo5 VALUES(1,1,1),(2.1,2.1,2.1),
    -> (3.123,3.123,3.123),(4.125,4.125,4.125),(5.115,5.115,5.115),
    -> (6.126,6.126,6.126),(7.116,7.116,7.116),(8.1151,8.1151,8.1151),
    -> (9.1251,9.1251,9.1251),(10.11501,10.11501,10.11501),
    -> (11.12501,11.12501,11.12501);

MariaDB [web1]> DESC demo5;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| a     | float(5,2)   | YES  |     | NULL    |       |
| b     | double(5,2)  | YES  |     | NULL    |       |
| c     | decimal(5,2) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

MariaDB [web1]> SELECT * FROM demo5;
+-------+-------+-------+
| a     | b     | c     |
+-------+-------+-------+
|  1.00 |  1.00 |  1.00 |
|  2.10 |  2.10 |  2.10 |
|  3.12 |  3.12 |  3.12 |
|  4.12 |  4.12 |  4.13 |
|  5.12 |  5.12 |  5.12 |
|  6.13 |  6.13 |  6.13 |
|  7.12 |  7.12 |  7.12 |
|  8.12 |  8.12 |  8.12 |
|  9.13 |  9.13 |  9.13 |
| 10.12 | 10.12 | 10.12 |
| 11.13 | 11.13 | 11.13 |
+-------+-------+-------+

结果说明（注意看）：
c是decimal类型，认真看⼀下输⼊和输出，发现decimal采⽤的是四舍五⼊
认真看⼀下a和b的输⼊和输出，float和double采⽤的是四舍六⼊五成双
decimal插⼊的数据超过精度之后会触发警告

什么是四舍六⼊五成双？
就是5以下舍弃5以上进位，如果需要处理数字为5的时候，需要看5后⾯是否还有不为0的任何数字，
如果有，则直接进位，9.1251如果没有，需要看5前⾯的数字，
若是奇数则进位    5.115
若是偶数则将5舍掉 4.125

示例2：
MariaDB [web1]> CREATE TABLE demo6(a float,b double,c decimal);
MariaDB [web1]> INSERT INTO demo6 VALUES(1,1,1),(1.234,1.234,1.4),
    -> (1.234,0.01,1.5);

MariaDB [web1]> SELECT * FROM demo6;
+-------+-------+------+
| a     | b     | c    |
+-------+-------+------+
|     1 |     1 |    1 |
| 1.234 | 1.234 |    1 |
| 1.234 |  0.01 |    2 |
+-------+-------+------+

a和b的数据正确插⼊，⽽c被截断了
浮点数float、double如果不写精度和标度，则会按照实际显⽰
decimal不写精度和标度，⼩数点后⾯的会进⾏四舍五⼊，并且插⼊时会有警告!


MariaDB [web1]> SELECT SUM(a),SUM(b),SUM(c) FROM demo6;
+--------------------+--------------------+--------+
| SUM(a)             | SUM(b)             | SUM(c) |
+--------------------+--------------------+--------+
| 3.4679999351501465 | 2.2439999999999998 |      4 |
+--------------------+--------------------+--------+
从上⾯sum的结果可以看出float、double会存在精度问题，decimal精度正常的，⽐如
银⾏对统计结果要求⽐较精准的建议使⽤decimal。

2.5 字符串类型

2.6 日期类型
2.6.1 获取当前时间
MariaDB [web1]> SELECT now();
+---------------------+
| now()               |
+---------------------+
| 2021-11-16 18:23:47 |
+---------------------+

2.6.2 now 与 sysdate
MariaDB [web1]> SELECT now(), sleep(3), now();
+---------------------+----------+---------------------+
| now()               | sleep(3) | now()               |
+---------------------+----------+---------------------+
| 2021-11-16 18:27:38 |        0 | 2021-11-16 18:27:38 |
+---------------------+----------+---------------------+

MariaDB [web1]> SELECT sysdate(), sleep(3), sysdate();
+---------------------+----------+---------------------+
| sysdate()           | sleep(3) | sysdate()           |
+---------------------+----------+---------------------+
| 2021-11-16 18:28:05 |        0 | 2021-11-16 18:28:08 |
+---------------------+----------+---------------------+

2.6.3 获取当前日期
MariaDB [web1]> SELECT curdate();
+------------+
| curdate()  |
+------------+
| 2021-11-16 |
+------------+

2.6.4 获取当前时间
MariaDB [web1]> SELECT curtime();
+-----------+
| curtime() |
+-----------+
| 18:30:18  |
+-----------+

2.6.5 UTC 日期时间函
MariaDB [web1]> SELECT utc_timestamp(), utc_date(), utc_time(), now();
+---------------------+------------+------------+---------------------+
| utc_timestamp()     | utc_date() | utc_time() | now()               |
+---------------------+------------+------------+---------------------+
| 2021-11-16 10:31:40 | 2021-11-16 | 10:31:40   | 2021-11-16 18:31:40 |
+---------------------+------------+------------+---------------------+

2.6.6 UNIX 时间戳
MariaDB [web1]> SELECT unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
|       1637058814 |
+------------------+

2.6.7 日期 时间戳 转换
MariaDB [web1]> SELECT unix_timestamp('2008-08-08');
+------------------------------+
| unix_timestamp('2008-08-08') |
+------------------------------+
|                   1218124800 |
+------------------------------+

MariaDB [web1]> SELECT unix_timestamp('2008-08-08 12:30:00');
+---------------------------------------+
| unix_timestamp('2008-08-08 12:30:00') |
+---------------------------------------+
|                            1218169800 |
+---------------------------------------+

MariaDB [web1]> SELECT from_unixtime(1218169800);
+---------------------------+
| from_unixtime(1218169800) |
+---------------------------+
| 2008-08-08 12:30:00       |
+---------------------------+

MariaDB [web1]> SELECT from_unixtime(1218169800,'%Y_%d_%m %h:%i:%s %x');
+--------------------------------------------------+
| from_unixtime(1218169800,'%Y_%d_%m %h:%i:%s %x') |
+--------------------------------------------------+
| 2008_08_08 12:30:00 2008                         |
+--------------------------------------------------+

2.6.8 时间加减
MariaDB [web1]> SELECT timestamp('2008-08-08');
+-------------------------+
| timestamp('2008-08-08') |
+-------------------------+
| 2008-08-08 00:00:00     |
+-------------------------+

MariaDB [web1]> SELECT timestamp('2008-08-08 08:00:00', '01:01:01');
+----------------------------------------------+
| timestamp('2008-08-08 08:00:00', '01:01:01') |
+----------------------------------------------+
| 2008-08-08 09:01:01                          |
+----------------------------------------------+

MariaDB [web1]> SELECT timestamp('2008-08-08 08:00:00', '10 01:01:01');
+-------------------------------------------------+
| timestamp('2008-08-08 08:00:00', '10 01:01:01') |
+-------------------------------------------------+
| 2008-08-18 09:01:01                             |
+-------------------------------------------------+

MariaDB [web1]> SELECT timestampadd(day, 1, '2008-08-08 08:00:00');
+---------------------------------------------+
| timestampadd(day, 1, '2008-08-08 08:00:00') |
+---------------------------------------------+
| 2008-08-09 08:00:00                         |
+---------------------------------------------+

MariaDB [web1]> SELECT date_add('2008-08-08 08:00:00', interval 10 day);
+--------------------------------------------------+
| date_add('2008-08-08 08:00:00', interval 10 day) |
+--------------------------------------------------+
| 2008-08-18 08:00:00                              |
+--------------------------------------------------+

2.6.9 相隔时间
MariaDB [web1]> SELECT timestampdiff(year,'2010-01-01','2021-01-01');
+-----------------------------------------------+
| timestampdiff(year,'2010-01-01','2021-01-01') |
+-----------------------------------------------+
|                                            11 |
+-----------------------------------------------+

MariaDB [web1]> SELECT timestampdiff(day,'2010-01-01','2021-01-01');
+----------------------------------------------+
| timestampdiff(day,'2010-01-01','2021-01-01') |
+----------------------------------------------+
|                                         4018 |
+----------------------------------------------+

MariaDB [web1]> SELECT timestampdiff(month,'2010-01-01','2021-01-01');
+------------------------------------------------+
| timestampdiff(month,'2010-01-01','2021-01-01') |
+------------------------------------------------+
|                                            132 |
+------------------------------------------------+

MariaDB [web1]> SELECT timestampdiff(hour,'2010-01-01','2021-01-01');
+-----------------------------------------------+
| timestampdiff(hour,'2010-01-01','2021-01-01') |
+-----------------------------------------------+
|                                         96432 |
+-----------------------------------------------+

MariaDB [web1]> SELECT datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00');
+--------------------------------------------------------+
| datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00') |
+--------------------------------------------------------+
|                                                      7 |
+--------------------------------------------------------+

MariaDB [web1]> SELECT timediff('08:08:08', '00:00:00');
+----------------------------------+
| timediff('08:08:08', '00:00:00') |
+----------------------------------+
| 08:08:08                         |
+----------------------------------+


2.6.10 返回当月最后一天
MariaDB [web1]> SELECT last_day('2021-02-10');
+------------------------+
| last_day('2021-02-10') |
+------------------------+
| 2021-02-28             |
+------------------------+

2.6.11 日期转换函数、时间转换函数
MariaDB [web1]> SELECT time_to_sec('01:00:00');
+-------------------------+
| time_to_sec('01:00:00') |
+-------------------------+
|                    3600 |
+-------------------------+

MariaDB [web1]> SELECT sec_to_time(3605);
+-------------------+
| sec_to_time(3605) |
+-------------------+
| 01:00:05          |
+-------------------+

MariaDB [web1]> SELECT to_days('2008-08-08');
+-----------------------+
| to_days('2008-08-08') |
+-----------------------+
|                733627 |
+-----------------------+

MariaDB [web1]> SELECT from_days(733627);
+-------------------+
| from_days(733627) |
+-------------------+
| 2008-08-08        |
+-------------------+

MariaDB [web1]> SELECT str_to_date('08/09/2008', '%m/%d/%Y');
+---------------------------------------+
| str_to_date('08/09/2008', '%m/%d/%Y') |
+---------------------------------------+
| 2008-08-09                            |
+---------------------------------------+

MariaDB [web1]> SELECT str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s');
+---------------------------------------------------------+
| str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s') |
+---------------------------------------------------------+
| 2008-08-09 08:09:30                                     |
+---------------------------------------------------------+

MariaDB [web1]> SELECT date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s');
+----------------------------------------------------+
| date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s') |
+----------------------------------------------------+
| 20080808222301                                     |
+----------------------------------------------------+

MariaDB [web1]> SELECT time_format('22:23:01', '%H.%i.%s');
+-------------------------------------+
| time_format('22:23:01', '%H.%i.%s') |
+-------------------------------------+
| 22.23.01                            |
+-------------------------------------+

MariaDB [web1]> SELECT makedate(2001,31);
+-------------------+
| makedate(2001,31) |
+-------------------+
| 2001-01-31        |
+-------------------+

MariaDB [web1]> SELECT maketime(12,15,30);
+--------------------+
| maketime(12,15,30) |
+--------------------+
| 12:15:30           |
+--------------------+