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 |
+--------------------+
