Mysql数据类型

整数类型

• bit、bool、tinyint、smallint、mediumint、int、bigint

  类型	说明	大小(字节)	例
  bit[(M)]	比特值类型	M默认1，范围[1,64]	最小1位，最大64位
  tinyint[(M)] [unsigned] [zerofill]	整型	单字节	有符号[-128,127]，无符号[0,255]
  bool,boolean	布尔值	单字节	建表自动转tinyint, 0.false，!0.true
  smallint[(M)] [unsigned] [zerofill]	整型	双字节	有符号[-32768,32767]，无符号[0,65535]
  mediumint[(M)] [unsigned] [zerofill]	整型	三字节	无符号[-8388608,8388607]，有符号[0,1677215]
  int[(M)] [unsigned] [zerofill]	整型	四字节	无符号[-2147483648,2147483647]，有符号[0,4294967295]
  bigint[(M)] [unsigned] [zerofill]	整型	八字节	无符号[-9223372036854775808,9223372036854775807],有符号[0, 18446744073709551615]
  serial	-	-	BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

有符号

• 默认都是有符号类型
• 注意不同类型的取值范围

create table demo1(
  c1 tinyint
); 
insert into demo1 values((-pow(2,7)), (pow(2,7)-1);
select * from demo1;
/*
+------+
| c1 |
+------+
| -128 |
| 127 |
+------+
2 rows in set (0.00 sec)
*/

类型(n)

• int(N) 无论N等于多少，int永远占4个字节
• N表示的现实宽度，不足的用0补足，超过的无视长度而直接显示整个数字，但要整型设置unsigned zerofill才有效
• 设置zerofill该字段默认被设置为无符号unsigned类型

CREATE TABLE test3 (
 `a` int,
 `b` int(5),
 `c` int(5) unsigned,
 `d` int(5) zerofill,  -- 默认设置为 d int(5) unsigned zerofill 无符号类型
 `e` int(5) unsigned zerofill,
 `f` int zerofill,
 `g` int unsigned zerofill
 );
insert into test3 values 
  (1,1,1,1,1,1,1),
  (11,11,11,11,11,11,11),
  (12345,12345,12345,12345,12345,12345,12345);
select * from test3;
/*
+-------+-------+-------+-------+-------+------------+------------+
| 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 |
+-------+-------+-------+-------+-------+------------+------------+
*/
show create table test3;
/*
| Table | Create Table 
| test3 | CREATE TABLE `test3` (
 `a` int(11) DEFAULT NULL,
 `b` int(5) DEFAULT NULL,
 `c` int(5) unsigned DEFAULT NULL,
 `d` int(5) unsigned zerofill DEFAULT NULL,
 `e` int(5) unsigned zerofill DEFAULT NULL,
 `f` int(10) unsigned zerofill DEFAULT NULL,
 `g` int(10) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*/

---

浮点数类型

• float、double、decimal
• float 单精度数值
• double 双精度数值
• decimal 定点型
• 浮点型和定点型可以用类型名称加(M,D)表示，M表示该值得总长度，D表示小数点后面的长度。
• float、double在不指定精度时，默认会按照实际的精度来显示，而decimal在不指定精度时，默认整数10，小数0。

  类型	说明	大小(字节)	例
  float[(M,D)] [unsigned] [zerofill]	单精度	四字节	[-3.402823466E+38,-1.175494351E-38]，0，[1.175494351E-38,3.402823466E+38]
  double[(M,D)] [unsigned] [zerofill]	双精度	八字节	[-1.7976931348623157E+308,-2.2250738585072014E-308]，0，[2.2250738585072014E-308, 1.7976931348623157E+308] 一般等价REAL[(M,D)] [UNSIGNED] [ZEROFILL]。如果SQL mode指定了REAL_AS_FLOAT，等价FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
  decimal[(M[,D])] [unsigned] [zerofill]	-	-	存储精确小数，M表示有效数字位数，范围是[1,65]，默认是10，D表示小数点后位数，范围是[0,30]，默认是0

create table test5(
  a float(5,2),
  b double(5,2),
  c decimal(5,2)
);
-- 插入测试数据
 insert into test5 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);
-- 查询
select * from test5;
/*
+-------+-------+-------+
| 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 |
+-------+-------+-------+
*/

• 示例总结：
  • decimal类型 采⽤的是四舍五⼊
  • float和double采⽤的是四舍六⼊五成双
    • 5以下舍弃
    • 5以上进位
    • 数字为5时，看5后⾯是否还有不为0的任何数字，如果有，则直接进位，如果没有，需要看5前⾯的数字，若是奇数则进位，若是偶数则将5舍掉
  • decimal插⼊的数据超过精度之后会触发警告

create table test6(
  a float,
  b double,
  c decimal
);
-- 插入数据
insert into test6 values 
  (1,1,1),
  (1.234,1.234,1.4),
  (1.234,0.01,1.5);
-- 查询数据
select * from test6;
/*
+-------+-------+------+
| a | b | c |
+-------+-------+------+
| 1 | 1 | 1 |
| 1.234 | 1.234 | 1 |
| 1.234 | 0.01 | 2 |
+-------+-------+------+
*/

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

 select sum(a),sum(b),sum(c) from test5;
/*
+--------+--------+--------+
| sum(a) | sum(b) | sum(c) |
+--------+--------+--------+
| 67.21 | 67.21 | 67.22 |
+--------+--------+--------+
*/
select sum(a),sum(b),sum(c) from test6;
/*
+--------------------+--------------------+--------+
| sum(a) | sum(b) | sum(c) |
+--------------------+--------------------+--------+
| 3.4679999351501465 | 2.2439999999999998 | 4 |
+--------------------+--------------------+--------+
*/

• float、double会存在精度问题，decimal精度正常。

---

字符串类型

• char、varchar、tinyblob、blob、mediumblob、longblob、tinytext、text、mediumtext、longtext

  类型	大小(字节)	用途
  char [CHARACTER SET charset_name] [COLLATE collation_name]	0-255	定长字符串
  varchar [CHARACTER SET charset_name] [COLLATE collation_name]	0-65535	变长字符串
  tinyblob	0-255	二进制字符串
  blob	0-65535	二进制形式长文本数据
  mediumblob	0-16777215	二进制形式中等长度文本数据
  longblob	0-4294967295	二进制形式极大文本数据
  tinytext [CHARACTER SET charset_name] [COLLATE collation_name]	0-255	短文本字符串
  text [CHARACTER SET charset_name] [COLLATE collation_name]	0-65535	长文本数据
  mediumtext [CHARACTER SET charset_name] [COLLATE collation_name]	0-1677215	中等长度文本数据
  longtext [CHARACTER SET charset_name] [COLLATE collation_name]	0-4294967285	极大文本数据
  enum('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]	枚举值 最多65535个值	实际上这个值小于3000
  set(‘value1’,‘value2’,…) [CHARACTER SET charset_name] [COLLATE collation_name]	最大64不同字符串对象	集合，包含一组字符串

---

日期

• date、datetime、timestamp、time、year

  日期类型	占用空间	日期格式	最小值	最大值	零值表示
  datetime	8 bytes	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59	0000-00-00 00:00:00
  timestamp	4 bytes	YYYY-MM-DD HH:MM:SS	19700101080001	2038 年的某个时刻	00000000000000
  date	4 bytes	YYYY-MM-DD	1000-01-01	9999-12-31	0000-00-00
  time	3 bytes	HH:MM:SS	-838:59:59	838:59:59	00:00:00
  year	1 bytes	YYYY	1901	2155	0000

• datetime 表示 年月日时分秒，是 DATE 和 TIME 的组合。
• timestamp
  • timestamp 表示 年月日 时分秒，但是记录的年份范围比较小
  • timestamp 和时区相关。插入日期时，会先转换为本地时区后再存放；查询日期时，会将日期转换为本地时区后再显示。
  • 表中的第一个 TIMESTAMP 列自动设置为系统时间（CURRENT_TIMESTAMP）。
  • 当插入或更新一行，但没有明确给 TIMESTAMP 列赋值，也会自动设置为当前系统时间。如果表中有第二个 TIMESTAMP 列，则默认值设置为0000-00-00 00:00:00。
  • TIMESTAMP 的属性受 Mysql 版本和服务器 SQLMode 的影响较大。
  • 如果记录的日期需要让不同时区的人使用，最好使用 TIMESTAMP。
• date 表示 年月日
• time 表示 时分秒
• year 表示 年份

---

总结

• 选小不选大：一般情况下选择可以正确存储数据的最小数据类型，越小的数据类型通常更快，占用磁盘，内存和CPU缓存更小。
• 简单就好：简单的数据类型的操作通常需要更少的CPU周期。
• 尽量避免NULL：尽量制定列为NOT NULL，除非真的需要NULL类型的值，有NULL的列值会使得索引、索引统计和值比较更加复杂。
• 浮点类型的建议统一选择decimal
• 记录时间的建议使用int或者bigint类型，将时间转换为时间戳格式，如将时间转换为秒、毫秒，进行存储，方便走索引

---

• 参考链接 https://blog.csdn.net/albertsh/article/details/94764106