自学内容网 自学内容网
自学内容网 > 正文

MySQL数据类型

🕗 发布于 2024-11-07 23:33 mysql  数据库  

文章目录

1. 数值数据类型

数据类型描述
BIT(size)位值类型。每个值的位数在 size 中指定。 size 参数可以保存从 1 到 64 的值。size 的默认值为 1。
TINYINT(size)一个非常小的整数。有符号范围是-128 到 127。无符号范围是 0 到 255。size 参数指定最大显示宽度（即 255）
BOOL零被认为是false，非零值被认为是true。
BOOLEAN等同 BOOL
SMALLINT(size)一个小整数。有符号范围是 -32768 到 32767。无符号范围是 0 到 65535。size 参数指定最大显示宽度（即 255）
MEDIUMINT(size)一个中等整数。有符号范围是 -8388608 到 8388607。无符号范围是 0 到 16777215。size 参数指定最大显示宽度（即 255）
INT(size)一个中等整数。有符号范围是 -2147483648 到 2147483647。无符号范围是 0 到 4294967295。size 参数指定最大显示宽度（即 255）
INTEGER(size)等于 INT(size)
BIGINT(size)一个大整数。有符号范围是-9223372036854775808到9223372036854775807。无符号范围是0到18446744073709551615。size参数指定最大显示宽度（即255）
FLOAT(size, d)一个浮点数。 size 中指定了总位数。小数点后的位数在 d 参数中指定。可以设计成无符号。此语法在 MySQL 8.0.17 中已弃用，并将在未来的 MySQL 版本中删除
FLOAT(p)一个浮点数。 MySQL 使用 p 值来确定是使用 FLOAT 还是 DOUBLE 作为结果数据类型。如果 p 是从 0 到 24，则数据类型变为 FLOAT()。如果 p 是从 25 到 53，则数据类型变为 DOUBLE()
DOUBLE(size, d)一个正常大小的浮点数。 size 中指定了总位数。 d参数中指定小数点后的位数
DECIMAL(size, d)一个精确的定点数。 size 中指定了总位数。小数点后的位数在 d 参数中指定。 size 的最大数为 65。d 的最大数为 30。size 的默认值为 10。d 的默认为 0。
DEC(size, d)等于 DECIMAL(size,d)

该表格来源于w3schools

1.1 测试tinyint类型

在数据范围内，全部ok

image-20241106214641041

超出范围，报错。这是因为MySQL增加了约束，保证了插入数据库的数据是可信的，不会像c/c++那样发生截断

image-20241106214751317

1.2 测试bit类型

image-20241106221243205

image-20241106221302287

可以看到，虽然插入值了，但是却看不到，这是因为bit字段在显示时，是按照ASCII码对应的值显示，如果我们插入65（A的ASCII是65）。

image-20241106221445468

注：如果使用Navicat17，是会显示的。

1.3 测试float类型

float(4,2)表示的范围是-99.99 ~ 99.99，MySQL在保存值时会进行四舍五入

image-20241107161204125

如果设置了无符号位，则不能插入负数，范围不变

ALTER TABLE t3 MODIFY num FLOAT(4,2) UNSIGNED;
INSERT INTO t3 VALUES(1, 99.99);
INSERT INTO t3 VALUES(1, 100);-- err, 超出范围
INSERT INTO t3 VALUES(2, 0);
INSERT INTO t3 VALUES(2, -0.01);-- err, 超出范围

image-20241107162117249

默认的FLOAT当长度超过7位后就不能保证正确性了

CREATE TABLE t4 (id INT, num FLOAT);
INSERT INTO t4 VALUES(1, 123456.7);
INSERT INTO t4 VALUES(2, 12345.6);
INSERT INTO t4 VALUES(3, 123457.78);
SELECT * FROM t4;

image-20241107163520289

1.4 测试decimal类型

decimal和float很像，但是float和decimal表示的精度不一样。

CREATE TABLE t5 (n1 FLOAT(10, 8), n2 DECIMAL(10, 8));
INSERT INTO t5 VALUES (23.12345612, 23.12345612);
SELECT * FROM t5;

image-20241107165135129

可以看到decimal的精度更高，如果希望小数的精度高，推荐使用decimal

2. 字符串数据类型

数据类型描述
CHAR(size)一个固定长度的字符串（可以包含字母、数字和特殊字符）。 size 参数指定以字符为单位的列长度 - 可以从 0 到 255。默认为 1
VARCHAR(size)可变长度字符串（可以包含字母、数字和特殊字符）。 size 参数指定字符的最大列长度的字节，最大长度65535个字节。
BINARY(size)等于 CHAR()，但存储二进制字节字符串。 size 参数以字节为单位指定列长度。默认为 1
VARBINARY(size)等于 VARCHAR()，但存储二进制字节字符串。 size 参数指定最大列长度（以字节为单位）。
TINYBLOB对于 BLOB（二进制大对象）。最大长度:255 字节
TINYTEXT保存一个最大长度为 255 个字符的字符串
TEXT(size)保存一个最大长度为 65,535 字节的字符串
BLOB(size)对于 BLOB（二进制大对象）。最多可容纳 65,535 字节的数据
MEDIUMTEXT保存最大长度为 16,777,215 个字符的字符串
MEDIUMBLOB对于 BLOB（二进制大对象）。最多可容纳 16,777,215 字节的数据
LONGTEXT保存最大长度为 4,294,967,295 个字符的字符串
LONGBLOB对于 BLOB（二进制大对象）。最多可容纳 4,294,967,295 字节的数据
ENUM(val1, val2, val3, …)只能有一个值的字符串对象，从可能值列表中选择。您最多可以在一个 ENUM 列表中列出 65535 个值。如果插入的值不在列表中，则将插入一个空白值。 这些值按您输入的顺序排序
SET(val1, val2, val3, …)可以有 0 个或多个值的字符串对象，从可能的值列表中选择。一个 SET 列表中最多可以列出 64 个值

2.1 测试char类型

CREATE TABLE IF NOT EXISTS t6 (id int, tmp CHAR(2));
INSERT INTO t6 VALUES (1, "a");
INSERT INTO t6 VALUES (2, "aa");
INSERT INTO t6 VALUES (3, "你");
INSERT INTO t6 VALUES (4, "你好");
INSERT INTO t6 VALUES (5, "aaa");-- err，超出范围
INSERT INTO t6 VALUES (6, "你好吗");  -- err，超出范围

image-20241107171010550

可以看到这里所谓的size不是字节，而是字符的个数，包括中英文。

2.2 测试varchar类型

关于varchar(size)，size到底是多大，这个size值，和表的编码密切相关：

  • varchar长度可以指定为0到65535之间的值，但是有1 - 3 个字节用于记录数据大小，所以说有效字节数是65532。
  • 当我们的表的编码是utf8时，varchar(n)的参数n最大值是65532/3=21844[因为utf中，一个字符占用3个字节]，如果编码是gbk，varchar(n)的参数n最大是65532/2=32766（因为gbk中，一个字符占用2字节）。
CREATE TABLE IF NOT EXISTS t7 (name VARCHAR(21845));-- 1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

CREATE TABLE IF NOT EXISTS t7 (name VARCHAR(21844));-- ok

CREATE TABLE IF NOT EXISTS t7 (id int, name VARCHAR(21844));   -- 1118 - Row size too large. ...

CREATE TABLE IF NOT EXISTS t7 (id int, name VARCHAR(21842));   -- ok

可以看到21844是在表没有其它列的情况下的最大值

char和varchar的对比

image-20241107202105297

如何选择定长或变长字符串？

  • 如果数据确定长度都一样，就使用定长（char），比如：身份证，手机号，md5
  • 如果数据长度有变化,就使用变长(varchar), 比如：名字，地址，但是你要保证最长的能存的进去。
  • 定长的磁盘空间比较浪费，但是效率高
  • 变长的磁盘空间比较节省，但是效率低
  • 定长的意义是，直接开辟好对应的空间
  • 变长的意义是，在不超过自定义范围的情况下，用多少，开辟多少

2.3 测试enum和set

  • enum: 该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值；而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,3,…最多65535个；当我们添加枚举值时，也可以添加对应的数字编号。
  • set: 位图结构，该设定只是提供了若干个选项的值，最终一个单元格中，设计可存储了其中任意多个值；而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,4,8,16,32，…最多64个。

有一个调查表votes，需要调查人的喜好， 比如（登山，游泳，篮球，武术）中去选择(可以多选)， （男，女）[单选]

CREATE TABLE t8 (name VARCHAR(20), gender ENUM('男', '女'), hobby SET('代码', '足球', '自行车', '跑步', '游泳', '拳击'));
INSERT INTO t8 VALUES ('张三', '男', '代码');
-- INSERT INTO t8 VALUES ('张三', 'or', '代码');     -- err
INSERT INTO t8 VALUES ('李四', '0' , '代码');     
INSERT INTO t8 VALUES ('王五', '2' , '代码');     
INSERT INTO t8 VALUES ('田七', '1' , '0');     
INSERT INTO t8 VALUES ('刘备', '1' , '1');        -- 000001, 代码
INSERT INTO t8 VALUES ('张飞', '1' , '2');        -- 000010, 足球
INSERT INTO t8 VALUES ('关羽', '1' , '63');       -- 111111, '代码', '足球', '自行车', '跑步', '游泳', '拳击'

image-20241107212158755

NULL和 空 是有区别的，上面的第4条插入李四和第6条插入田七时，由于下标选的是0，插入的是空。如果插入时不指定其他列

INSERT INTO t8 (name) VALUES ('曹操');
SELECT * FROM t8;

image-20241107212646670

则都是NULL，NULL是没有，而空指的是空字符串。

注：不建议在添加枚举值，集合值的时候采用数字的方式，因为不利于阅读

查询爱好为’代码’的人

image-20241107214450469

查询爱好包含’代码’的人，这里要用到一个函数

find_in_set(sub,str_list) ：如果 sub 在 str_list 中，则返回下标；如果不在，返回0；str_list 用逗号分隔的字符串。
例如:

SELECT FIND_IN_SET('a','a,b,c');-- 返回1
SELECT FIND_IN_SET('b','a,b,c');-- 返回2

所以可以写成

SELECT * FROM t8 WHERE FIND_IN_SET('代码',hobby);

image-20241107214910367

如果有多个条件，可以使用and连接

SELECT * FROM t8 WHERE FIND_IN_SET('代码',hobby) AND FIND_IN_SET('足球',hobby);-- 爱好包含 代码 和 足球 的

image-20241107215112821

3. 日期和时间数据类型

数据类型描述
DATE日期。格式:YYYY-MM-DD。支持的范围是从 ‘1000-01-01’ 到 ‘9999-12-31’，占用3字节
DATETIME(fsp)日期和时间组合。格式:YYYY-MM-DD hh:mm:ss。支持的范围是从"1000-01-01 00:00:00"到"9999-12-31 23:59:59"。 在列定义中添加 DEFAULT 和 ON UPDATE 以获得自动初始化并更新到当前日期和时间，占用8字节
TIMESTAMP(fsp)时间戳。 TIMESTAMP 值存储为自 Unix 纪元 (‘1970-01-01 00:00:00’ UTC) 以来的秒数。格式:YYYY-MM-DD hh:mm:ss。支持的范围是从 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-09 03:14:07’ UTC。 可以使用列定义中的 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 指定自动初始化和更新到当前日期和时间，占用4字节
TIME(fsp)一次。格式:hh:mm:ss。支持的范围是从 ‘-838:59:59’ 到 ‘838:59:59’
YEAR四位数格式的年份。允许采用四位数格式的值:1901 到 2155 和 0000。 MySQL 8.0 不支持两位数格式的年份。

测试DATE，DATETIME，TIMESTAMP

CREATE TABLE IF NOT EXISTS t7 (t1 DATE, t2 DATETIME, t3 TIMESTAMP);
INSERT INTO t7 (t1, t2) VALUES ('2004-04-25', '2002-02-22 14:22:22');
SELECT * FROM t7;

image-20241107204006339

UPDATE t7 SET t2 = '2003-01-11 11:11:11';
SELECT * FROM t7;

image-20241107204123868

可以看到TIMESTAMP类型的t3随着数据的修改而更新


原文地址：https://blog.csdn.net/Suinnn/article/details/143608662

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2024