MySQL中的数据类型总结和测试代码

---

0 前言

总结了MySQL中的数据类型，大家也可以在MySQL中查看帮助手册。并且建了一些表的样例且给出了部分测试代码，大家可以输入到MySQL中进行验证。我会及时回复评论区的问题，如果觉得本文有帮助欢迎点赞 😃。

1 数据类型

1.1 整数类型

1. 如果要表示小数只能用浮点数类型，整数类型不能表示小数。

2. 浮点类型DOUBLE精度比FLOAT类型高，如果需要精确到10位以上，就应该选择DOUBLE类型。

1.2 浮点类型

对于精度要求较高的时候需要使用定点数存，因为定点数内部是以字符串形式存储的。

1.3 字符串类型

1. CHAR定长字符串，占用空间大，速度快。
2. VARCHAR变长字符串，占用空间小，速度慢。
3. TEXT类型是一种特殊的字符串类型。只能保存字符数据，而且不能有默认值。
4. 它们3个存储和检索数据的方式都不一样，数据检索的效率CHAR>VARCHAR>TEXT。
5. CHAR在保存的时候，后面会用空格填充到指定的长度，在检索的时候后面的空格会去掉。
6. VARCHAR在保存的时候，不进行填充。当值保存和检索时尾部的空格仍保留。

1.4 日期时间类型

YEAR只保存年份，占用空间小。其它和日期时间有关的可以通过整型保存时间戳，方便计算。

1.5 实例

建立一个学员表，提前选择合适的数据类型：



可以先在编辑器中写好代码，再输入到MySQL中。先建立一个学员表：

CREATE DATABASE IF NOT EXISTS `DAY1` ;
# 最好将名称加上反引号
USE `DAY1`;
-- user
-- id
-- username
-- age
-- sex
-- email
-- addr
-- birth
-- salary
-- tel
-- married
-- 当需要使用中文时，需要临时转换编码方式
-- SET NAMES GBK;
-- 字段注释 通过COMMENT 注释内容 给字段添加注释
CREATE TABLE IF NOT EXISTS `user`(
id SMALLINT,
username VARCHAR(20),
age TINYINT,
sex ENUM('男','女','保密'),
email VARCHAR(50),
addr VARCHAR(200),
birth YEAR,
salary FLOAT(8,2),
tel INT,
married TINYINT(1) COMMENT '0代表未结婚，非0代表已婚'
)ENGINE=INNODB;

创建新闻表，查看表结构：

-- 创建新闻表 cms_news
-- 编号、新闻标题、新闻内容、新闻发布时间、点击量、是否置顶
CREATE TABLE IF NOT EXISTS cms_news(
id INT,
title VARCHAR(50),
content TEXT,
pubTime INT,
clickNum INT,
isTop TINYINT(1) COMMENT '0代表不置顶，1代表置顶'
);

-- 查看cms_news表的表结构
DESC cms_news;
DESCRIBE cms_news;
SHOW COLUMNS FROM cms_news;

2 测试各种类型的数据

2.1 测试整型

-- 测试整型
CREATE TABLE test1(
num1 TINYINT,
num2 SMALLINT,
num3 MEDIUMINT,
num4 INT,
num5 BIGINT
);
-- 向表中插入记录INSERT tbl_name VALUE|VALUES(值,...);
INSERT test1 VALUES(-128,-32768,-8388608,-2147483648,-9223372036854775808);

INSERT test1 VALUES(-129,-32768,-8388608,-2147483648,-9223372036854775808);

-- 查询表中所有记录SELECT * FROM tbl_name;
SELECT * FROM test1;

2.2 无符号UNSIGNED

-- 无符号UNSIGNED

CREATE TABLE test2(
num1 TINYINT UNSIGNED,
num2 TINYINT 
);

INSERT test2 VALUES(0,-12);

2.3 零填充ZEROFILL

-- 零填充ZEROFILL

CREATE TABLE test3(
num1 TINYINT ZEROFILL,
num2 SMALLINT ZEROFILL,
num3 MEDIUMINT ZEROFILL,
num4 INT ZEROFILL,
num5 BIGINT ZEROFILL
);

INSERT test3 VALUES(1,1,1,1,1);
INSERT test3 VALUES(123,1,1,1,1);

2.4 测试浮点类型

-- 测试浮点类型

CREATE TABLE test4(
num1 FLOAT(6,2),
num2 DOUBLE(6,2),
num3 DECIMAL(6,2)
);
INSERT test4 VALUES(3.1415,3.1415,3.1415);

INSERT test4 VALUES(3.1495,3.1495,3.1495);

2.5 测试CHAR和VARCHAR

-- 测试CHAR和VARCHAR
CREATE TABLE IF NOT EXISTS test5(
str1 CHAR(5),
str2 VARCHAR(5)
);
INSERT test5 VALUES('1','1');

INSERT test5 VALUES('12345','12345');

INSERT test5 VALUES('123456','123456');


INSERT test5 VALUES('','');

INSERT test5 VALUES('1  ','1  ');

INSERT test5 VALUES('  a','  a');

INSERT test5 VALUES('啊啊啊啊啊','麦子学院好');

CREATE TABLE test6(
str1 TEXT
);

INSERT test6 VALUES('skdfjlksdfjlksjdflkj塑料口袋精灵是看见对方离开首都基辅绿卡时间的联发科技');

2.6 测试枚举类型

-- 测试枚举类型
CREATE TABLE IF NOT EXISTS test7(
sex ENUM('男','女','保密    ')
);
INSERT test7 VALUES('男     ');

INSERT test7 VALUES('女     ');

INSERT test7 VALUES('保密');

INSERT test7 VALUES('保密1');

INSERT test7 VALUES(2);

INSERT test7 VALUES(0);

INSERT test7 VALUES(NULL);

INSERT test7 VALUES('');

2.7 测试集合类型

-- 测试集合类型

CREATE TABLE IF NOT EXISTS test8(
fav SET('A','B','C','D')
);

INSERT test8 VALUES('A,C,D');

INSERT test8 VALUES('D,B,A');

INSERT test8 VALUES(3);

INSERT test8 VALUES(15);

2.8 测试日期时间

-- 测试日期时间
CREATE TABLE IF NOT EXISTS test9(
birth YEAR
);

INSERT test9 VALUES(1901);
INSERT test9 VALUES(2155);
INSERT test9 VALUES(2156);

CREATE TABLE IF NOT EXISTS test10(
test TIME
);
INSERT test10 VALUES('1 12:12:12');

INSERT test10 VALUES('11:11');

CREATE TABLE IF NOT EXISTS test11(
test DATE
);
INSERT test11 VALUES('12-6-7');