mysql 数据库字段类型大全

数值
tinyint
1.十分小的数据,占用1个字节，有符号的范围是-128 - 127，无符号的范围是 从 0 到 255 的整型数据
2.java类型Integer

smallint
1.较小的数据,占用2个字节
2.java类型Integer

mediumint
1.中等大小的数据,占用3个字节
2.java类型Integer

int
1.标准的整数，占用4个字节，范围为-231~231 -1 (-2147483648 ~ 2147483647)，可以达到21亿，一般足够用了
2.unsigned，无符号，范围大小 是 0 到4294967295 (40亿)
id int unsigned NOT NULL AUTO_INCREMENT
3.int(num)一般加上zerofill，才有效果。zerofill的作用一般可以用在一些编号相关的数字中，比如学生的编号 001 002 … 999这种
id int(4) unsigned zerofill NOT NULL AUTO_INCREMENT
4.java类型Integer

bigint
1.占用8 个字节，范围从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据(所有数字)，无符号的范围是0到18446744073709551615

float
1.浮点型，占4个字节
2.float单精度在一些处理器上比double双精度更快而且只占用double双精度一半的空间
3.缺点是当值很大或很小的时候，它将变得不精确。
4.float(m,d)，一个显示宽度m和一个小数点后面带的个数d，同decimal的参数一样
5.unsigned和 zerofill 修饰符也可以被 float、double和 decimal数据类型使用, 并且效果与 int数据类型相同
6.java类型Float

double
1.浮点型，占8个字节
2.double 双精度是消耗内存的,并且是 float 单精度的两倍
3.当你需要保持多次反复迭代的计算精确性时，或在操作值很大的数字时，双精度型是最好的选择。
4.double(m,d),一个显示宽度m和一个小数点后面带的个数d，同decimal的参数一样
5.unsigned和 zerofill 修饰符也可以被 float、double和 decimal数据类型使用, 并且效果与 int数据类型相同
6.java类型Double

decimal
1.字符串形式的浮点数，一般用于计算金额,decimal 类型是适合财务和货币计算
2.decimal(m,d)，m是数字的最大位数，他的范围是从1-65；d是小数点后的位数，他的范围是0-30，并且不能大于m。
3.如果m被省略了，那么m的值默认为10，如果d被省略了，那么d的值默认为0.
比如：decimal(5,2)，最大位数5位，2位小数，范围-999.99~999.99
4.小数点左侧位数1–2占1个字节，3-4占2个字节，5-6占3个字节，7-9占4个字节
5.unsigned和 zerofill 修饰符也可以被 float、double和 decimal数据类型使用, 并且效果与 int数据类型相同
6.java类型BigDecimal

字符串
char
1.固定大小的字符串，0~255
2.java类型String

varchar
1.可变大小的字符串，0~65535
2.java类型String

tinytext
1.微型文本，最大长度是 255 (2^8 – 1) 个字符。
2.java类型String

text
1.文本串， 最大长度是 65535 (2^16 – 1) 个字符，64kb
2.java类型String

MEDIUMTEXT
1.文本串， 最大长度是 16777215 (2^24 – 1) 个字符，16M
2.java类型String

LONGTEXT
1.文本串， 最大长度是 4294967295 (2^32 – 1) 个字符，4GB
2.java类型String

注意：text类型可用于存储富文本，在utf8mb4下，英文占用1个字节，一般汉字占3个字节，emoji表情占4个字节。
1
二进制字符串类型
BINARY与VARBINARY类型
BINARY和VARBINARY类似于CHAR和VARCHAR，只是它们存储的是二进制字符串。
BINARY (M)为固定长度的二进制字符串，M表示最多能存储的字节数，取值范围是0~255个字符。如果未指定(M)，表示只能存储1个字节。例如BINARY (8)，表示最多能存储8个字节，如果字段值不足(M)个字 节，将在右边填充’\0’以补齐指定长度。
VARBINARY(M)为可变长度的二进制字符串，M表示最多能存储的字节数，总字节数不能超过行的字节长度限制65535，另外还要考虑额外字节开销，VARBINARY类型的数据除了存储数据本身外，还需要1或2个字节来存储数据的字节数。VARBINARY类型必须指定(M) ，否则报错。
BLOB类型
(binary large object)，二进制大对象，是一个可以存储二进制文件的容器，在数据库中存放体积较大的多媒体对象就是应用程序处理BLOB的典型例子。
TinyBlob 最大 255，Blob 最大 65K，MediumBlob 最大 16M，LongBlob 最大 4G
需要注意的是，在实际工作中，往往不会在MySQL数据库中使用BLOB类型存储大对象数据，通常会将图片、音频和视频文件存储到服务器的磁盘上 ，并将图片、音频和视频的访问路径存储到MySQL中。
日期时间
date
1.YYYY-MM-DD日期格式，范围1000-01-01 ~ 9999-12-31
2.java类型Date

time
1.HH：mm：ss时间格式，范围-838:59:59 ~ 838:59:59
2.java类型Time

DATETIME
1.YYYY-MM-DD HH：mm：ss (最常用的时间格式)，范围1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
2.java类型Date

TIMESTAMP
1.时间戳， 1970.1.1到现在的毫秒数！也较为常用

DATETIME和TIMESTAMP的区别
DATETIME 类型在所有的日期时间类型中占用的存储空间最大，总共需要 8 个字节的存储空间
不考虑存储空间优化的情况下，DATETIME是最优的时间存储类型 ；考虑存储空间的情况下，TIMESTAMP是最优的时间存储类型
TIMESTAMP会先将用户输入转换成UTC时间进行存储，查询时也会将时间转换成当前UTC时间，所以当数据库服务器的时区跟查询客户端时区不一致时，会出现查询不到的情况。比如查询客户端与数据库服务器都在中国，北京是东八区时间是UTC+8小时，那么存储时会先-8小时，查询时+8。
使用函数CURRENT_TIMESTAMP(), NOW()可以向datetime类型字段插入系统当前日期和时间。
YEAR[(2|4)]
1.缺省为4位格式,4位格式取值范围为 1901 ~ 2155,2位格式取值范围为 70-69(1970-2069)
2.java类型Year

ENUM
1.枚举类型，ENUM类型只允许从成员中选取单个值，不能一次选取多个值
2.当ENUM类型包含1～255个成员时，需要1个字节的存储空间；当ENUM类型包含256～65535个成员时，需要2个字节的存储空间。ENUM类型的成员个数的上限为65535个。
3.没有限制非空的情况下，可以添加null值
4.java类型Enum

JSON类型
JSON（JavaScript Object Notation）是一种轻量级的数据交换格式 。简洁和清晰的层次结构使得 JSON成为理想的数据交换语言

CREATE TABLE json_demo(content JSON);

insert into json_demo(content)values('{"name":"baidu","host":"www.baidu.com"}'),
('{"name":"alibaba","host":"www.alibaba.com"}');

select JSON_EXTRACT(content,'$.name')as name,JSON_EXTRACT(content,'$.host')as host from json_demo;

建表实例
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `demo`;
CREATE TABLE `demo` (
`id` varchar(36) NOT NULL COMMENT 'id',
`code` int(4) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT '四位编号',
`name` varchar(100) COMMENT '姓名',
`id_number` char(18) COMMENT '证件号码，使用固定18位，可以提供索引效率',
`birth_year` year COMMENT '出生年份',
`sex` ENUM('男','女') COMMENT '出生年份',
`resume` text COMMENT '个人简介',
`create_date` date COMMENT '创建日期',
`create_time` time COMMENT '创建时间',
`update_time` datetime COMMENT '更新时间',
`is_del` tinyint NOT NULL DEFAULT 0 COMMENT '是否删除 0-正常1-已删除',
`salary` decimal(11, 2) COMMENT '工资收入 9+2整数9位占用4个字节',
`x_coordinate` float COMMENT 'x坐标',
`y_coordinate` double COMMENT 'y坐标',
PRIMARY KEY (`id`),
INDEX `demo_id_number`(`id_number`),
);

SET FOREIGN_KEY_CHECKS = 1;

uuid()
36位的id，建议直接使用这种

select UUID()
44a5cc70-e23c-11ec-bff7-00ffc0087494

32位的id，但是这种可能会出现重复的情况，

select replace(uuid(),'-','');
8ea9402be23c11ecbff700ffc0087494

mysql自动创建uuid
oracel创建uuid为主键的表时可以直接指定default uuid();但是mysql不支持，可以通过触发器实现

CREATE TRIGGER `demo_before_insert` BEFORE INSERT ON `demo` FOR EACH ROW
BEGIN
IF new.id is NULL THEN
SET new.id = UUID();
END IF;
END;

或使用mybatisplus注解

import com.baomidou.mybatisplus.annotations.TableId;
import com.baomidou.mybatisplus.enums.IdType;
@ApiModelProperty("主键ID")
@TableId(
type = IdType.UUID
)
private String id;