MySQL(一)：数据类型

最新推荐文章于 2024-04-25 15:04:34 发布
最新推荐文章于 2024-04-25 15:04:34 发布
阅读量252 收藏
点赞数
分类专栏： mysql 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/GDUT_Trim/article/details/115522242
版权

MySql

数据类型

UNSIGNED

UNSIGNED属性就是将数字类型无符号化，无符号化的话就只有正数。例如INT，如果没有加UNSIGNED那么它的范围就是 − 2 31 -2^{31} −231~ 2 31 2^{31} 231（INT有4个字节，也就是32位，减去符号位，只有31位），而INT UNSIGNED的范围为0~ 2 32 2^{32} 232,因为去掉符号位就有32位了。

这看起来是一个不错的选项，特别是对住像主键这种自增的东西，开发者都希望为整数，但UNSIGNED可能会带来一些负面的影响。

比如下面这个栗子

CREATE TABLE  t(
	a INT UNSIGNED,
	b INT UNSIGNED
)ENGINE=INNODB;
INSERT INTO t SELECT 1,2;
SELECT * FROM t;
SELECT a-b FROM t;

执行最后一条select语句的结果是
在这里插入图片描述其实结果在不同的系统可能还会不一样

可以是 − 1 -1 −1，可以是一个很大的正值，也可能会报错，报错就是如上，out of range超出了范围。

原因其实是，这里 1 − 2 1-2 1−2变成十六进制的结果是 0 x F F F F F F F F 0xFFFFFFFF 0xFFFFFFFF，再转变成二进制其实是一串1， 11111111.... 11111111.... 11111111....，然后再将其变为十进制，如果是有符号位，那么前面的1代表是负数，然后取反加1，结果就是-1，如果前面没有符号位，那就表示一个很大的正数。

ZEROFILL

ZEROFILL像是一个长度属性，关系到的是数字类型后面的长度值，比如int(10)，通常像这种类型后面添加的参数不是代表字符个数就是字节个数，那么整形应该已经是限定死是4个字节的，那这里的10代表什么？其实如果没有ZEROFILL这个属性，这里的10没有任何意义。

现在我们对上面创建的表进行修改,修改成int(4)和加上zerofill属性

ALTER TABLE t CHANGE COLUMN a a INT(4) UNSIGNED ZEROFILL;
//再进行查询
SELECT * FROM t;

在这里插入图片描述
ZEROFILL的效果其实就是如果宽度小于设定的宽度，则会进行自动填充0，要注意的是，这里只是最后显示的结果，在Mysql中实际存储的还是1，可以使用hex函数来证明
在这里插入图片描述
可以看到HEX(a)为1而不是0001，所以里面储存的还是1，而不是0001，其实也可以设想一下，如果数据库内部存储的是0001这样的字符串，又怎么进行整形的加减乘除呢？

SQL_MODE模式的设置

SQL_MODE是比较容易让人忽略的一个常量，默认为空，SQL_MODE的设置其实是一种比较冒险的设置，因为在这种设置下，可以允许一些非法操作，比如可以将NULL插入到非NULL的字段中，甚至可以插入一些非法日期，比如 “2012-12-32”，因此最好将这个值设为严格模式。

对于SQL_MODE的设置，可以在Mysql的配置文件如my.cnf和my.ini中进行，也可以在客户端的工具中进行，并且可以分别进行全局的设置或者当前会话的设置。下面的语句可以用来查看当前SQL_MODE的设置情况。

//查看全局
SELECT @@global.sql_mode;
//查看当前会话
SELECT @@session.sql_mode;

在这里插入图片描述
在这里插入图片描述

日期和时间类型

Mysql数据中总共有5种于日期和时间有关的数据类型

类型所占空间
DATETIME8个字节
TIMESTAMP4个字节
DATE3个字节
TIME3个字节
YEAR1个字节
DATITIME和DATE

DATETIME是占用空间最多的一种类型，占用了8个字节，可以显示日期同时也可以显示时间，其可以表达的范围为1000-01-01 00:00:00到9999-12-31 23:59:59。而DATE只可以表示日期，不可以显示时间，其可以表达的范围为1000-01-01到9999-12-31。

在mysql数据库中，对日期和输入格式的要求是十分宽松的，以下的输入都可以视为日期类型

2011-01-01 00:01:00
2011/01/01 00+01+10
20110101000110
11/01/01 00@01@10

其中，最后一种类型的年上面的11是表示离当前时间最近的年份。

SELECT CAST('11/01/01 00@01/10' AS DATETIME) AS datetime;

在这里插入图片描述数据库的日期类型不能精确到微秒级别，任何的微妙数值都会被数据库截断。

CREATE TABLE t2(
	a DATETIME
)
//插入微妙级别的数据
INSERT INTO t2 SELECT '2011-01-01 00:01:10.123456';
//查询插入的数据
SELECT a FROM t2;

在这里插入图片描述不过mysql数据库提供了函数MICROSECOND函数来提取日期中的微秒值。

SELECT MICROSECOND('2011-01-01 00:01:10.123456');

在这里插入图片描述mysql的CAST函数在强制转换到DATETIME时也会截断毫秒数，在插入之后同样会截断。

mysql从5.6.4版本开始，mysql增加了对秒的小数部分（fractional second)的支持，其具体语法是type_name(fsp)。

其中，type_name可以是TIME、DATETIME、和TIMESTAMP。fsp表示支持秒的小数部分的精度，最大为6，精确到微秒；默认是为0，表示没有小数部分，同时这也是为了兼容之前的DATETIEM和TIMESTAMP的用法，而对于一些时间函数，也增加了对fsp的支持，比如CURTIME()、SYSDATE()和UTC_TIMESTAMP()。

SELECT CURTIME(4) AS 'time';

在这里插入图片描述#### TIMESTAMP

TIMESTAMP和DATETIME显示的效果是一样的，都是固定的YYYY-MM-DD HH:MM:SS类型，但TIMESTAMP只有4个字节，那是因为显示范围的不同，TIMESTAMP的显示范围是从UNIX的时间纪元开始（1970-01-01 00:00:00 UTC）到2038-01-19 03:14:07 UTC（Integer类型是4个字节，也就是32位，能表示的最长时间是68年，UTC是世界统一时间），而其实际存储的内容是1970-01-01 00:00:00到当前时间的毫秒数。

TIMESTAMP和DATETIME的不同

  1. 在建表时，列为TIMESTAMP类型的可以设置默认值而DATETIME不行

    //虽然执行这条语句不会报错，但表中的a列信息还是没有默认值
CREATE TABLE t3(
	a DATETIME DEFAULT '2011-01-01 00:01:10'
)
//不会报错，且有默认值信息
CREATE TABLE t4(
	b TIMESTAMP DEFAULT '2011-01-01 00:01:10'
)

  2. 在更新表时，可以设置TIMESTAMP类型的列自动更新为当前时间

    CREATE TABLE t5(
	a INT ,
	b TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
INSERT INTO t5(a) VALUES(1);
SELECT * FROM t5;
//执行UPDATE时更新为当前时间的例子
CREATE TABLE t6(
	a INT,
    //设置b字段为update操作时，修改为当前时间
	b TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
INSERT INTO t6 SELECT 1,CURRENT_TIMESTAMP;
//过一段时间
UPDATE t6 SET a = 2 WHERE a = 1;
SELECT * FROM t6;

    但要注意的是，要发生数据变化才会修改，即如果执行update操作，但数据还是不变，那么也不会进行更新的。原本是可以在建表时将TIMESTAMP的列设为一个默认值为更新时的时间，已经取消了。

YEAR和TIME

YEAR类型只会占用一个字节(也就是8位，可以储存范围为0~255)，并且在定义时可以指定显示的宽度为YEAR(4)或者YEAR(2)（现在YEAR(2)已经被删除了，所以下面只讨论YEAR(4)）。

YEAR(4)其显示年份为1901~2155

TIME类型占用3个字节，显示的范围为-839:59:59~838:59:59，TIME在时位上可以超过23，因为它不但可以表示一天的时间，也可以表示时间间隔（几天或者一个月）,同时这也表示了为什么会有负数，TIME和DATETIME类型一样，也可以显示微秒时间MISCOPESECOND()，也一样会被掐断。

与日期相关的函数
NOW、CURRENT_TIMESTAMP和SYSDATE

这三个函数都是返回当前时间，并且有一点区别

SELECT NOW(),CURRENT_TIMESTAMP(),SYSDATE();

SLEEP前
在这里插入图片描述SLEEP后
在这里插入图片描述SYSDATE明显睡了两秒，也就是说，返回的是执行到函数的时间

时间加减函数

无论是时间类型还是时间函数类型，都不可以简单使用加减号来进行运算,如果使用加减号来进行运算，会发现只是简单地按位对应来加减而已，没有返回正确的时间类型。

CREATE TABLE t7(
	a TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	b TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
INSERT INTO t7 VALUES();
SELECT a+b,a,b FROM t7;
SELECT NOW(),NOW()+0;

在这里插入图片描述
在这里插入图片描述具体使用过的方法是DATE_ADD(date,INTERVAL expr unit)和DATE_SUB(date,INTERVAL,expr,unit)，分别是假发和减法(INTERVAL是间隔的意思)

SELECT NOW() AS cur,DATE_ADD(NOW(),INTERVAL 1 DAY) AS tomorrow,DATE_SUB(NOW(),INTERVAL 1 DAY) AS yesterday;

在这里插入图片描述其中expr值可以是hi一个负数，因此单纯使用DATE_ADD也可以完成DATE_SUB的操作。

SELECT NOW() AS cur,DATE_ADD(NOW(),INTERVAL -1 DAY) AS yesterday;

在这里插入图片描述如果是闰月的话（比如不是闰年的2月28日加上4年，就会变成闰年的2月29日），DATE_ADD函数返回的日期是2月29日，如果不是闰月，那么返回的日期是2月28日。

SELECT DATE_ADD('2000-02-29',INTERVAL 4 YEAR);

在这里插入图片描述

SELECT DATE_ADD('2000-02-29',INTERVAL 5 YEAR);

在这里插入图片描述除了DAY和YEAR之外，还有其他类型的，比如MICROSECOND(微秒会被截断)、SECOND(秒)、MINUTE(分钟)、HOUR、WEEK、MONTH等类型

DATE_FORMAT函数

按照用户的需求格式化打印出日期

SELECT DATE_FORMAT(NOW(),"%Y%m%d");

在这里插入图片描述

数字类型

整形

整形类型有INT、SMALLINT、TINYINT、MEDIUMINT和BIGINT。

类型占用空间（字节）最小值最大值
TINYINT1-128（有符号位）255
SMALLINT2-32768(-2^15)2^16
MEDIUNINT3-2^232^24
INT4-2^312^32
BIGINT8-2^632^64

对于整形，前面有两个相关的属性就是ZEROFILL和UNSIGNED，一旦开启了ZEROFILL那么UNSIGNED也会自动开启。

浮点型（非精确类型）

Mysql支持两种浮点类型，单精度的FLOAT类型以及双精度的DOUBLE PRECISION类型。这两种类型都是非精确的类型，即经过一些操作之后并不能保证运算的准确性，例如M*G/G不一定会等于M。

FLOAT类型用于表示近似数值数据类型，SQL标准允许在关键字FLOAT后面的括号内用位来指定精度（但不能为指数范围）。Mysql还支持可选的只用于确定存储大小的精度规定，0到23的精度对应FLOAT列的4字节单精度，24到53的精度对应DOUBLE列的8字节双精度。

Mysql还允许使用非标准语法，FLOAT(M,D)或REAL(M.D)或DOUBLE PRECISION(M,D)。这里的(M,D)表示该值一共显示M位整数，其中D位是小数点后面的位数。例如FLOAT(7,4)的一个列可以显示为-999.9999。如果超出了范围，Mysql保存值时会进行四舍五入，比如在FLOAT(7,4)中插入999.00009，会变成999.0001。

Mysql将DOUBLE视为DOUBLE PRECISION(非标准扩展)的同义词，将REAL视为DOUBLE PRECISION（非标准扩展）的同义词，即REAL和DOUBLE都是DOUBLE PRECISION，若将Mysql服务器的模式设置，REAL_AS_FLOAT，那这时REAL会被视为FLOAT类型。

为了保证最大的可以可移植性（保证精确性，因为如果不满长FLOAT参数里面的长度会进行补充），需要使用近似数值数据值存储的代码，使用FLOAT或DOUBLE PRECISION，并不规定精度或者位数。

高精度类型

DECIMAL和NUMERIC类型在Mysql中被视为相同的类型，用于保存必须为确切精度的值，比如工资，当声明该类型的列时，通常必须指定精度和标度（精度指数字位数即保存值的主要位数，标度指小树点后面可以保存的位数），例如 salary DECIMAL(5,2) ，精度为5，标度为2。在标准SQL中，语法DECIMAL(M)等价于DECIMAL(M,0)，M默认是10的。

CREATE TABLE t10(
	a DECIMAL
)
SHOW CREATE TABLE t10;

在这里插入图片描述DECIMAL或NUMERIC的最大位数是65位，但具体的DECIMAL或NUMERIC列的实际范围受具体列的精度或标度约束。如果分配给此类列的值的小数点后位数超过指定的标度允许的范围，值将按该标度进行转换，即列的标度大于指定标度，可以按指定标度来进行转化（一般列允许的标度跟操作系统有关，和mysql版本）。

位类型

位类型，即BIT数据类型可用来保存位字段的值，BIT(M)类型表示允许存储M位数值，M范围为1到64，占用的空间为（M+7）/8字节。如果为BIT(M)列分配的值的长度小于M位，会在值得左边用0来填充，例如BIT(6)列分配一个值b’101’，其效果与分配b’000101’相同（这里b代表是BIT类型，位类型），要指定类型，可以使用b’value’符

CREATE TABLE t11(
	a BIT(4)
)
INSERT INTO t11 SELECT b'1000';
SELECT HEX(a),a FROM t11;

在这里插入图片描述#### 字符类型

字符集

我们首先来认识一下字符集，字符集其实由字符组成的，通俗讲是字符集合嘛，比如什么什么支持gbk字符集，那么他就可以识别出gbk字符集里面包含的字符，然后来认识一下字符编码，字符编码其实是由字符集来支持的，字符编码其实是由字符和它对应的唯一二进制编码组成的，即形成一个映射关系，比如gbk字符集支持Unicode编码，那么gbk里面的字符就可以对应Unicode里面的映射关系，变成二进制，然后储存在计算机中，我们所说的乱码情况，就是字符集使用了不支持的编码方式，导致解码错误，也就是映射关系出现问题。

对于简体中文，我们习惯使用gbk或gb2312，这两者的关系是gbk是gb2312的超集，因此可以支持更多的汉字，不过当前mysql不支持中文字符集gb18030，因此在有些应用中已经出现gbk不能显示特定中文字体的情况了，如果使用繁体中文的话，big5是首选的字符集。

Unicode和utf8的区别

Unicode是一种在计算机上使用的字符编码，为每种语言的每个字符设定了统一且唯一的二进制编码，utf8是字符集。对于Unicode编码的字符集，尽量将所有CHAR字段设置为VARCHAR字段，因为对于CHAR字段，数据库会保存最大可能的字节数，比如CHAR(30)，字段中可以储存的字符长度为30，但表中使用的字符集允许一个字符占的最大字节为3，那么CHAR(30)，数据库可能存储90字节的数据。

对字符集的设置可以在Mysql的配置文件中完成(my.ini)
在这里插入图片描述mysql可以细化每个对象字符集的设置

CREATE TABLE t12(
	a VARCHAR(10) CHARSET gbk,
	b VARCHAR(10) CHARSET latin1,
	c VARCHAR(10)
)CHARSET=utf8;

c列这里没有设置字符集，所以会使用表的字符集utf8，如果表也没有字符集，那就会使用架构（数据库）时指定的字符集，如果在创建数据库中也没有规定字符集，那就会使用数据库配置文件中指定的字符集。

排序规则

排序规则是指对指定字符集下不同字符的比较规则，其特征有以下几点。

  • 两个不同的字符集可能有相同的排序规则
  • 每个字符集有一个默认的排序规则
  • 常用的命令规则
    • _ci(case insensitive)表示大小写不敏感
    • _cs(case sensitive)表示大小写敏感
    • _bin(binary)表示二进制的比较
SHOW CHARSET; //可以查看Mysql支持的所有字符集，里面的Default_collation就是默认支持的排序规则

在这里插入图片描述

SHOW COLLATION; //查看支持的各种排序规则

在这里插入图片描述

SHOW COLLATION LIKE "gbk%"; //查找关于gbk字符集的排序规则

在这里插入图片描述如果需要查看gbk字符集默认的排序规则

SHOW CHARSET LIKE "gbk%";

在这里插入图片描述
可以看到，gbk默认的比较方式是gbk_chinese_ci，跟上一章表Default字段为yes的对应。

排序规则有什么影响

CREATE TABLE t13(
	a VARCHAR(10)
)CHARSET=utf8;

INSERT INTO t13 SELECT "a";
INSERT INTO t13 SELECT "A";
SELECT * FROM t13 WHERE a = "a";

最后一条查询语句的结果是下图所示
在这里插入图片描述
再看看utf8的默认排序规则

SHOW CHARSET LIKE utf8

在这里插入图片描述可以看到utf8默认的排序规则是_ci，也就是大小写不敏感，所以对于语句where a = “a”，并不会区分大小写，将A也搜索了出来。

再查查

SELECT "a" = "A"; //查看当前会话的排序规则

在这里插入图片描述
可以看到返回值为1，所以认为这两个字符的比较结果是相等的，如果要进行区分大小写，可以设置当前会话的排序规则，语句为SET NAMES … COLLATE …

SET NAMES utf8 COLLATE utf8_bin //修改为二进制排序规则

在这里插入图片描述
此时，返回值就为0了，数据库认为a和A是不同的字符，但如果需要对表中字段进行区分，需要将指定列的排序规则进行修改

ALTER TABLE t13 MODIFY COLUMN a VARCHAR(10) COLLATE utf8_bin;
//再次执行
SELECT * FROM t13 WHERE a = 'A';

在这里插入图片描述
排序规则不仅会影响大小写的比较问题，也会影响着索引。

//将a列修改回原来定义的排序规则
ALTER TABLE t13 MODIFY COLUMN a VARCHAR(10) COLLATE utf8_general_ci;
//给a加一个唯一索引
ALTER TABLE t13 ADD UNIQUE KEY(a);

结果
在这里插入图片描述
因为大小写不敏感，所以存在着列a中是存在2个相同的值的，所以无法进行添加唯一索引。。索引是B+树，加索引时会进行比对。

CHAR和VARCHAR

这是两种最常用的字符串类型，一般来说CHAR(N)是用来保存固定长度的字符串，VARCHAR(N)用来保存可变长字符类型，对于CHAR类型，N的范围为0 ~ 255（ 2 8 2^8 28），对于VARCHAR类型，N的取值范围为0 ~ 65535（ 2 16 2^{16} 216），两种类型的N都代表字符长度，而不是字节长度。

注：不过在mysql4.1之前的版本，N是代表字节长度。

对于CHAR类型的字符串，Mysql数据库会自动对存储列的右边进行填充(Right Padded)操作，直到字符串达到指定的长度N，而在读取该列时，mysql数据库会自动将填充的字符删除（所以建议如果是固定长度的字符串才使用CHAR，不固定的使用VARCHAR）,有一种情况例外，可以将SQL_MODE设置为PAD_CHAR_TO_FULL_LENGTH。

ALTER TABLE t13 MODIFY COLUMN a CHAR(10);
SELECT a,HEX(a),LENGTH(a) FROM t13;
//修改Sql_mode模式
SET sql_mode='pad_char_to_full_length';
SELECT a,HEX(a),LENGTH(a) FROM t13;

在这里插入图片描述
LENGTH()函数返回的是字节长度而不是字符长度（一个汉字是两个字节），CHAR_LENGTH()函数返回的是字节长度，因此可以得出CHAR类型会进行填满的（如果没有改变SQL_MODE,会将填充的进行删除，所以返回的不会是10，而是3），对于多字节字符集，CHAR(N)长度的列最多可占用的字节数为该字符集单字符最大的字节数 * 字符长度，例如在utf8下，CHAR(10)最多可能占用30个字节，因为utf8单个字符可以占最大3个字节。

SELECT @a:='MySql技术内幕';
SELECT @a,HEX(@a),LENGTH(@a),CHAR_LENGTH(@a);

在这里插入图片描述
在utf8字符集下，一个汉字占3个字节，gbk下，一个汉字占2个字符。

CREATE TABLE t14(
	a VARCHAR(10) CHARSET utf8,
	b VARCHAR(10) CHARSET gbk
)
INSERT INTO t14 SELECT "MySql计数内幕","MySql计数内幕";
SELECT a,LENGTH(a),CHAR_LENGTH(a),b,LENGTH(b),CHAR_LENGTH(b) FROM t14;

在这里插入图片描述
VARCHAR类型储存变长字段的字符类型，与CHAR类型不同的是，其存储时需要在浅醉长度列表上加上实际存储的字符，该字符会占用1~2字节的空间，具体的规则如下，当储存的字符串长度小于255（ 2 8 2^8 28）时，其需要1个字节空间，当大于255( 2 8 2^8 28)字节时，需要2字节的空间。所以对于lantin1字符集（单个字符占最大的字节为1）来说，CHAR(10)和VARCHAR(10)占用最大字节是不同的，CHAR(10)肯定是10个字节，但VARCHAR(10)至少为11个字节，如果为VARCHAR(255)甚至会12个字节，因为需要额外的字节来存放字符长度。

虽然CHAR和VARCHAR的存储方式不太相同，但是对于两个字符串的比较，都只比较其值，忽略CHAR值存在的右填充，即使将SQL_MODE设置为PAD_CHAR_TO_FULL_LENGTH也一样。

BINARY与VARBINARY

这两个与CHAR和VARCHAR比较类似，唯一不同的是BINARY与VARBINARY存储的是二进制的字符串（存储的是二进制，图片，视频那些），而且BINARY与VARBINARY没有字符集的概念，对其排序和比较都是按照二进制值进行对比的，

BINARY(N)和VARBINARY(N)中的N是指字节长度（CHAR,VARCHAR的N是字符长度）,对于BINARY(10),其可存储的字节固定为10，而对于CHAR(10),其存储的字符长度为10，但字节长度不一样（需要知道字符集单个字符所占用的最大字节）。

CREATE TABLE t15(
	a BINARY(1)
)ENGINE=INNODB CHARSET=GBK;
//修改为gbk字符集
SET NAMES GBK;
//插入数据
INSERT INTO t15 SELECT '我';
SELECT a,HEX(a) FROM t15;

结果
在这里插入图片描述
在这里插入图片描述
在表中，字段a是一个BINARY(1)占一个字节，而在GBK字符集中，一个汉字占两个字节，所以根本不够内存，所以在插入时会给出警告，提示字符被截断，如果SQL_MODE为严格模式，则会直接报错。

//修改a列为2字节
ALTER TABLE t15 MODIFY COLUMN a BINARY(2);
SELECT a,HEX(a) FROM t15;

在这里插入图片描述CHAR VARCHAR和BINARY VARBINARY的不同之处

SELECT a,LENGTH(a) FROM t15;

在这里插入图片描述
首先第一个不同之处就是参数N，CHAR和VARCHAR的N是字符长度，而BINARY和VARBINARY的N是字节长度。第二个不同的是CHAR和VARCHAR进行比较时，比较的是存储的字符，忽略字符后的填充字符，而对于BINARY和VARBINARY来说，由于是按照二进制值来进行比较的（BINARY也会进行填充，而且比较时并不会忽略填充字符，由上图可知），因此结果会不同。

SELECT HEX('a'),HEX('a	'),'a'='a    ';

在这里插入图片描述

SELECT HEX(BINARY('a')),HEX(BINARY('a	')),BINARY('a')=BINARY('a    ');

在这里插入图片描述

BLOB和TEXT

BLOB是用来存储二进制大数据类型的（比如图片和视频），根据储存长度的不同，BLOB可细分为以下4中类型，括号中的数代表存储的字节数。

  1. TINYBLOB( 2 8 2^8 28)

  2. BLOB( 2 16 2^{16} 216)

  3. MEDIUMBLOB( 2 24 2^{24} 224)

  4. LONGBLOB( 2 32 2^{32} 232)

TEXT类型同BLOB一样，也可以细分为4种类型

  1. TINYTEXT( 2 8 2^8 28)
  2. TEXT( 2 16 2^{16} 216)
  3. MEDIUMTEXT( 2 24 2^{24} 224)
  4. LONGTEXT( 2 32 2^{32} 232)

在大多数情况下，可以将BLOB类型的列视为足够大的VARBINARY类型的列。同样，也可以将TEXT类型的列视为足够大的VARCHAR类型的列，然而，在BLOB和TEXT在以下几方面又不同于VARBINARY和VARCHAR

  • 在BLOB和TEXT类型的列上创建索引时，必须指定索引前缀的长度，而VARCHAR和VARBINARY的前缀长度是可选的（索引前缀其实是适用于很长的数据，因为数据很长，会让索引变得也很长（索引要进行比对的），所以要规定前缀索引来索引开始的一部分字符，如果前面一部分字符相同，那就是匹配成功，这样可以大大节约索引空间，从而提高索引效率，但这样会降低索引的选择性即可能存在匹配错误情况，前面一部分匹配成功，但后面不正确）
  • BLOB和TEXT类型的列不能有默认值
  • 在排序时，只是用列的前max_sort_length个字节（默认值为1024，该参数是一个动态参数，可以在客户端进行更改）

在数据库中，最小的存储单位是页（也可以称为块）。为了有效存储列类型为BLOB或者TEXT的大数据类型，一般将这种属性的列的值存放在行溢出页中，而数据页存储的行数据只包含BLOB或TEXT类型数据列的前一部分数据。

数据页是由许多的行数据组成，每行数组由各个列组成，对于列类型为BLOB的数据，InnoDB存储引擎只存储前20字节，而该列的完整数据则存放在BLOB的行溢出页中，在这种方式下，数据页中能存放大量的行数据，从而提高了数据的查询效率

此外，InnoDB引擎会将大VARCHAR类型字符串，比如VARCHAR(65530),也就是VARCHAR最大字符长度( 2 16 2^{16} 216)，会自动转化为TEXT或者BLOB类型。

在这里插入图片描述

ENUM和SET类型

ENUM和SET都是集合类型，不同的是ENUM类型最多可以枚举65536（ 2 16 2^{16} 216）个元素，而SET类型中最多枚举64个元素( 2 6 2^6 26)。

由于MySQL不支持传统的CHECK约束，因此通过ENUM和SET类型并结合SQL_MODE可以解决一部分问题，例如，表中有一个"性别"列，规定域的范围只能是"male"或者"female"，在这种情况下可以通过ENUM类型结合严格的SQL_MODE模式来进行约束

CREATE TABLE t16(
	user_name VARCHAR(33),
	sex ENUM("male","female")
)ENGINE=INNODB
//设置为严格模式
SET sql_mode = 'strict_trans_tables';
INSERT INTO t16 SELECT 'David','male';
INSERT INTO t16 SELECT 'Mike','female';
//下面这条会插入失败
INSERT INTO t16 SELECT 'John','bimale';

在这里插入图片描述

GDUT_Ember
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MYSQl字符串类型
潜心种树
08-26 1548
CHAR与VARCHARChar：长度固定为创建时声明的长度，长度范围为 0~255 Varchar:长度可变，长度为0~255(5.0.3以前，以后为0~65535)Char插入时会把尾部空格删除 Varchar会保留空格测试代码：Create table vc(v VARCHAR(4) c CHAR(4)); Insert into vc values('ab ','ab ');
mysql字符类型_MYSQL字符类型大全
weixin_42515225的博客
01-18 1936
=============MySQL数据类型=============整数类型========整数类型 字节数 无符号范围 有符号的范围TINYINT 1 0~255 -128~127SMALLLINT 2 0~65535 -32768~327...
MySQL数据类型：字符串类型详解
最新发布
常备不懈
04-25 1807
在MySQL数据库中，字符串数据类型用于存储各种文本信息。这些数据类型主要包括`CHAR`、`VARCHAR`、`TEXT`和`BLOB`等。
Mysql 数据库字符类型详解
aini4568的博客
12-30 264
MySQL 中提供了多种对字符数据的存储类型，不同的版本可能有所差异。以5.0 版本为例，MySQL 包括了CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和SET 等多种字符串类型。表3-4 中详细列出了这些字符类型的比较。 下面将分别对这些字符串类型做详细的介绍。 1. CHAR 和VARCHAR 类型　　CHAR 和VARCHAR ...
mysql字符类型
ygzn_5118的专栏
02-27 540
一、查看表结构 1、desc t1; 2、explain t1; 3、show columns from t1; 二、查看表的定义 show create table t1; 三、char and varchar varchar(n)  n的取值范围是0~65535    (在一个表中varchar 的长度总和不能超过65535) 当n的值小于255时，mysql额外需要1个字节去
MySQL的字符类型
icy_whisper的博客
09-27 311
MySQL的字符类型 字符类型 MySQL提供了多种关于字符存储的类型，但是在大多数情况下我们只使用char和varchar即可。 类型 大小 用途 CHAR 0 - 255字节 定长字符串 VARCHAR 0 - 65535 字节 变长字符串 TINYBLOB 0 - 255字节 不超过 255 个字符的二进制字符串 TINYTEXT 0 - 255字节 短文本字符串 BLOB 0 - 65 535字节 二进制形式的长文本数据 TEXT 0 - 65 535字节 长文本
MYSQL基础：数据类型.docx
08-08
MYSQL基础：数据类型 MySQL 是一种常用的关系型数据库管理系统，广泛应用于各种应用程序和网站开发中。在 MySQL 中，数据类型是定义和存储数据的重要组成部分。 MySQL 支持的各种数据类型可以分为数值类型、字符串...
MySql数据类型
12-14
MySQL 数据类型是数据库设计的基础，它决定了数据的存储方式、取值范围以及占用空间。以下是对各个数据类型的详细解释： 1. **整型（xxxint）**：MySQL 提供了多种整数类型，如 TINYINT、SMALLINT、MEDIUMINT、INT ...
mysql数据类型.doc
11-30
MySQL 是一个功能强大且广泛使用的关系数据库管理系统，它提供了多种数据类型来存储不同的数据。了解 MySQL 的数据类型对于数据库设计和开发非常重要。本文将详细介绍 MySQL 的各种数据类型，包括整数类型、浮点数...
MySQL中数据类型的验证
09-10
主要介绍了MySQL中数据类型的验证 的相关资料,需要的朋友可以参考下
Java中数据类型和MYSQL中数据类型的对比
08-18
首先，Java 有两类数据类型：基本数据类型（如 int、char、float）和引用数据类型（如类、接口、数组）。基本数据类型是预定义的，它们的大小和值的范围是固定的。例如，`int` 在 Java 中占用 4 个字节，可存储 -2^...
mysql数据库——字符类型
hhyiyuanyu的博客
07-09 1150
-- 字符类型-- CHAR(定长字符串),VARCHAR（变长字符串）,-- TEXT(一个较大块文本数据，不区分大小写，非二进制的),-- BLOB（超过范围的就会被自动截断，区分大小写，二进制的）;可以添加TINY，LONG等修饰-- varbinary(二进制可变长度字符串)，-- enum(枚举,多个字符的选择，只能选择其中的一个),set(插入一个或多个)create table ta...
MySQL数据类型之字符型
morris
05-15 4722
字符类型 类型 说明 N的含义 是否有字符集 最大长度 char(n) 定长字符 字符 是 255 varchar(n) 变长字符 字符 是 65535 binary(n) 定长二进制字节 字节 否 255 varbinary(n) 变长二进制字节 字节 否 65535 tinyblob 二进制大对象 字节 否 255 blob(n) 二进制大对象 字节 否 65535 mediumblob(n) 二进制大对象 字节 否 16M longblob(n) 二进制大对
mysql字符类_MySQL的字符类型
weixin_35068573的博客
01-19 319
MySQL的字符类型字符类型MySQL提供了多种关于字符存储的类型，但是在大多数情况下我们只使用char和varchar即可。类型大小用途CHAR0 - 255字节定长字符串VARCHAR0 - 65535 字节变长字符串TINYBLOB0 - 255字节不超过 255 个字符的二进制字符串TINYTEXT0 - 255字节短文本字符串BLOB0 - 65 535字节二进制形式的长文本数据TEXT...
Mysql字符串类型
逐梦追风_博客
02-28 322
MySQL提供了多种对字符数据的存储类型，不同的版本可能有所差异。以5.0版本为例，MYSQL包括了CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET等多种字符串类型。下表详细列出了这些字符串的比较。 字符串类型 字节 描述和存储需求 CHAR(M) M M为0~255之间的整数 VARCHAR(M) M为0~65535之间的整数...
mysql数据库数据类型之字符型
ices_的博客
12-01 1650
mysql 字符类型总结
丶博弈
12-10 2512
https://blog.csdn.net/jaryle/article/details/52025023 (tinyint(x)的作用) https://blog.csdn.net/ZBylant/article/details/86572567 (int(x)的作用) https://liuchenyang0515.blog.csdn.net/article/details/117524328 (varchar(x)的作用) https://blog.csdn.net/qq_30336433/a.
mysql ------ 字符串类型
梦情与你的博客
01-27 224
大家好，今天我们总结一下字符串类型的知识点。 字符串类型 大小 描述 CHAR(n) 0-255字节 定长字符串 VARCHAR(n) 0-65535 字节 变长字符串 TINYBLOB 0-255字节 不超过 255 个字符的微小二进制字符串 TINYTEXT 0-255字节 短文本微小字符串 BLOB 0-65 535字节 二进制形式的长文本数据 TEXT ...
MySQL中你可能忽略的COLLATION实例详解
菜鸟教程
05-06 1421
文章来源： 学习通http://www.bdgxy.com/普学网http://www.boxinghulanban.cn/智学网http://www.jaxp.net/ 前言 MySQL 数据库的字符串类型有 CHAR、VARCHAR、BINARY、BLOB、TEXT、ENUM、SET。不同的类型在业务设计、数据库性能方面的表现完全不同，其中最常使用的是 CHAR、VARCHAR。今天我就带你深入了解字符串类型 CHAR、VARCHAR 的应用。 CHAR 和 VARCHAR 的定义 CHAR(N)
MySQL实用：数据类型详解与NULL值
MySQL实用技术-MySQL数据类型.pptx#资源达人分享计划#是一份深入讲解MySQL数据库中各类数据类型的教程，主要涵盖了以下几个关键知识点： 1. 数值列类型：MySQL支持两种主要的数值类型：整型和浮点型。整型包括...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值