MySQL 데이터 유형 및 저장 메커니즘에 대한 자세한 설명

이 문서의 디렉터리:
1.1 데이터 유형 개요
1.2 저장 메커니즘 및 작동 방법
　1.2.1 정수 저장 방법
　1.2.2 문자 유형 저장 방법
　1.2.3 날짜 및 시간 저장 방법
　1.2.4 ENUM 데이터 유형
　1.2.5 SET 데이터 유형
1. 3. 데이터 유형 속성: unsigned
1.3. 데이터 유형 속성: zerofill

1.1 데이터 유형 개요

데이터 유형은 각 필드에 저장할 수 있는 데이터의 종류와 저장할 수 있는 데이터의 양을 제한하는 필드 제약 조건입니다. 저장할 수 있는 형식 등 MySQL/MariaDB에는 대략 5가지 유형의 데이터 유형, 즉 정수, 부동 소수점, 문자열 유형, 날짜 및 시간 유형, 특수 ENUM 및 SET 유형이 있습니다.

이 5가지 데이터 유형의 의미, 제한 사항 및 관련 지침은 아래 그림과 같습니다.

각 데이터 유형이 차지하는 바이트 수는 mariadb 공식 매뉴얼을 참조하세요.

1.2 저장 메커니즘 및 연산 방법

데이터 유형에 따라 필드의 데이터 저장 길이가 제한될 수 있는 이유는 테이블이 생성될 때 메모리에서 주소 공간이 엄격하게 구분되고, 이에 따라 몇 바이트를 저장할 수 있기 때문입니다. 주소 공간 데이터의 길이. 물론 이는 매우 조잡한 개념이다. 보다 구체적인 저장 방법은 아래 설명을 참조한다.

데이터 유형의 범위를 제한하는 방법에는 두 가지가 있습니다. 하나는 공간을 엄격하게 제한하고, 공간을 분할한 만큼만 데이터를 저장할 수 있으며, 초과되는 데이터는 잘라냅니다. 다른 하나는 추가 바이트 비트를 사용하여 표시하는 것입니다. 데이터가 특정 주소 공간의 바이트에 저장되어 있는지 여부를 저장하면 표시되고, 저장되지 않으면 표시되지 않습니다.

1.2.1 정수 저장 방법

여기서는 주로 정수 저장 방법을 설명합니다. 부동 소수점 데이터 유형의 저장 방법은 고려해야 할 사항이 너무 많습니다.

정수 데이터 형식의 경우 공간을 엄격하게 제한하지만 문자와는 다릅니다. 나누어진 각 바이트의 비트에 있는 0과 1을 직접 값으로 계산할 수 있으므로 범위는 비트 수로 계산됩니다. . 1바이트는 8비트를 가지며, 이 8비트는 2^8=256개의 값을 구성할 수 있습니다. 마찬가지로 2바이트는 총 2^16=65536개의 값을 가지며, 범위는 0입니다. -2^32. 즉, 0~255 사이의 숫자는 1바이트만 차지하고 256~65535 사이의 숫자는 2바이트가 필요합니다.

MySQL/mariadb의 정수 데이터 유형은 매개변수 M을 사용할 수 있다는 점에 유의해야 합니다. M은 INT(M),tinyint(M)와 같은 양의 정수입니다. 이 M은 표시 길이를 나타냅니다. 예를 들어 int(4)는 출력 중에 4자리 정수가 표시된다는 의미입니다. 실제 값의 자릿수가 표시 값 너비보다 작으면 공백을 사용하여 채웁니다. 기본적으로 왼쪽. 결과 자릿수가 제한을 초과해도 표시 결과에는 영향을 미치지 않습니다. 일반적으로 이 함수는 공백 대신 0으로 채우기 위해 zerofill 속성과 함께 사용됩니다. 그러나 zerofill을 사용한 후에는 열이 자동으로 부호 없는 필드가 됩니다. 예:

CREATE TABLE test3(id INT(2) ZEROFILL NOT NULL);INSERT INTO test3 VALUES(1),(2),(11),(111);SELECT id FROM test3;
+-----+
| id  |
+-----+
|  01 |
|  02 |
|  11 |
| 111 |
+-----+
4 rows in set (0.00 sec)

유념할 점은 디스플레이 너비는 디스플레이 효과에만 영향을 미치며 저장, 비교, 길이 계산 등과 같은 작업에는 영향을 미치지 않는다는 것입니다..

1.2.2 문자형 저장 방법

여기에서는 주로 char와 varchar의 저장 방법과 차이점을 설명합니다.

Char 유형은 "고정 길이 문자열 유형"이라고도 합니다. 공백 길이를 엄격하게 제한하지만 바이트 수가 아닌 문자 수를 제한합니다. 그러나 이전 버전에서는 바이트 수가 제한되었습니다. 제한된 . 따라서 char(M)은 M자를 엄격하게 저장하고, 부족한 부분은 공백으로 채우고, M자를 초과하는 부분은 바로 잘라낸다.

char 유형에는 "짧을 때 공백으로 채우는" 기능이 있으므로 데이터의 신뢰성을 반영하기 위해 주소 공간에서 데이터를 검색할 때 후행 공백이 자동으로 삭제됩니다. 이는 char의 특별한 기능입니다. 수동으로 저장한 후행 공백도 자동으로 완성된 것으로 간주되어 검색 중에 삭제됩니다. 즉, where 문의 name='gaoxiaofang '和name='gaoxiaofang' 결과는 동일합니다.

예:

create table test2(a char(4) charset utf8mb4);insert into test2 values('恭喜你'),('恭喜你成功晋级'),('hello'),('he    ');select concat(a,'x') from test2;+---------------+| concat(a,'x') |
+---------------+| 恭喜你x       |
| 恭喜你成x     |
| hellx         |
| hex           |
+---------------+4 rows in set

위 결과에서 볼 수 있듯이 char(4)는 4자만 저장할 수 있으며, 후행 공백이 제거됩니다.

varchar常被称为"变长字符串类型"，它存储数据时使用额外的字节的bit位来标记某个字节是否存储了数据。每存储一个字节(不是字符)占用一个bit位进行记录，因此一个额外的字节可以标记共256个字节，2个额外的字节可以标记65536个字节。但MySQL/mariadb限制了最大能存储65536个字节。这表示，如果是单字节的字符，它最多能存储65536个字符，如果是多字节字符，如UTF8的每个字符占用3个字节，它最多能存储65536/3=21845个utf8字符。

因此，varchar(M)存储时除了真实数据占用空间长度，还要额外计算1或2个字节的Bit位长度，即对于单字节字符实际占用的空间为M+1或M+2个字节，对于多字节字符(如3字节)实际占用的空间为M*3+1或M*3+2个字节。

由于varchar存储时需要采用额外的bit位记录每一个字节，短了的数据不会自动使用补齐，因此显式存储的尾随空格也会被存储并在Bit位上进行标记，也就是说不会删除尾随空格。

和char(M)一样，当指定varchar(2)时，只能存储两个字节的字符，如果超出了，则切断。

关于char、varchar以及text字符串类型，它们在比较时不会考虑尾随空格，但做like匹配或正则匹配时会考虑空格，因为匹配时字符是精确的。例如：

create table test4(a char(4),b varchar(5));insert into test4 values('ab ','ab   ');select a='ab   ',b='ab      ',a=b from test4;+-----------+--------------+-----+| a='ab   ' | b='ab      ' | a=b |
+-----------+--------------+-----+|         1 |            1 |   1 |
+-----------+--------------+-----+1 row in setselect a like 'ab      ' from test4;+-------------------+| a like 'ab      ' |
+-------------------+|                 0 |
+-------------------+1 row in set

最后需要说明的是，数值在存储(或调入内存)时，以数值型方式存储比字符型或日期时间类型更节省空间。因为整数值存储时是直接通过bit计算数值的，0-255之间的任意整数都只占一个字节，256-65535之间的任意整数都占2个字节，而占用4个字节时便可以代表几十亿个整数之间的任意一个，这显然比字符型存储时每个字符占用一个字节节省空间的多。例如值"100"存储为字符型时占用三个字节，而存储为数值型将只占用一个字节。因此数据库默认将不使用引号包围的值当作数值型，如果明确要存储为字符型或日期时间型则应该使用引号包围以避免歧义。

1.2.3 日期时间型的存储方式

日期时间性数据存储时需要使用引号包围，避免和数值类型的数据产生歧义。关于日期时间的输入方式是非常宽松的，以下几种方式都是被允许的：任意允许的分隔符，建议使用4位的年份。

20110101
2011-01-01 18:40:20
2011/01/01 18-40-20
20110101184020

1.2.4 ENUM数据类型

ENUM数据类型是枚举型。定义方式为ENUM('value1','value2','value3',...)，在向该类型的字段中插入数据时只能插入value中的某一个或NULL，插入其他值或空(即'')时都将截断为空数据。存储时会忽略大小写(将转换为ENUM中的字符)，且会截断尾随空格。

mysql> create table test6(id int auto_increment primary key,name char(20),gender enum('Mail','f'));
mysql> insert into test6(name,gender) values('malongshuai','Mail'),('gaoxiaofang','F'),('wugui','x'),('tuner',null),('woniu','');
Query OK, 5 rows affected
Records: 5  Duplicates: 0  Warnings: 2mysql> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1265 | Data truncated for column 'gender' at row 3 |
| Warning | 1265 | Data truncated for column 'gender' at row 5 |
+---------+------+---------------------------------------------+2 rows in setmysql> select * from test6;
+----+-------------+--------+
| id | name        | gender |
+----+-------------+--------+
|  1 | malongshuai | Mail   |
|  2 | gaoxiaofang | f      |
|  3 | wugui       |        |
|  4 | tuner       | NULL   |
|  5 | woniu       |        |
+----+-------------+--------+5 rows in set

ENUM类型的数据存储时是通过index数值进行存储的，相比于字符串类型，它只需要1或2个字节进行存储即可。理论上，当value的数量少于256个时只需一个字节，超出256个但少于65536个时使用2个字节存储。MySQL/MariaDB限制最多只能存储65536个value。当然，这是理论上的限制，实际存储时要考虑的因素有很多，例如NULL也会占用bit位，所以实际存储时可能250个value就需要2个字节。

ENUM的每个value都通过index号码进行编号，无论是检索还是操作该字段时都会通过index的值来操作。value1的index=1，value2的index=2，依次类推。但需要注意有两个特殊的index值：NULL值的index=NULL，空数据的index=0。

例如ENUM('a','b','c')，向该字段依次插入'','b','a','c',NULL,'xxx'时，由于第一个和最后一个都会截断为空数据，所以它们的index为0，插入的NULL的index为NULL，插入的'b','a','c'的index值分别为2,1,3。所以index号码和值的对应关系为：

index	value
NULL	NULL
0	''
0	''
1	'a'
2	'b'
3	'c'

使用ENUM的index进行数据检索：

mysql> select * from test6 where gender=2;
+----+-------------+--------+
| id | name        | gender |
+----+-------------+--------+
|  2 | gaoxiaofang | f      |
+----+-------------+--------+1 row in set

特别建议，不要使用ENUM存储数值，因为无论是排序还是检索或其他操作，都是根据index值作为条件的，这很容易产生误解。例如，下面是用ENUM存储两个数值，然后进行检索和排序操作。

mysql> create table test7(id enum('3','1','2'));
mysql> insert into test7 values('1'),('2'),('3');# 检索时id=2，但结果查出来却为1，因为id=2的2是enum的index值，在enum中index=2的值为1mysql> select * from test7 where id=2;
+----+
| id |
+----+
| 1  |
+----+1 row in set# 按照id进行排序时，也是通过index大小进行排序的mysql> select * from test7 order by id asc;
+----+
| id |
+----+
| 3  |
| 1  |
| 2  |
+----+3 rows in set

因此，强烈建议不要在ENUM中存放数值，即使是浮点型数值也很容易出现歧义。

1.2.5 SET数据类型

对于SET类型，和enum类似，不区分大小写，存储时删除尾随空格，null也是有效值。但不同的是可以组合多个给出的值。如set('a','b','c','d')可以存储'a，b','d，b'等，多个成员之间使用逗号隔开。所以，使用多个成员的时候，成员本身的值中不能出现逗号。如果要存储的内容不在set列表中，则截断为空值。

SET数据类型占用的空间大小和SET成员数量M有关，计算方式为(M+7)/8取整。所以： 1-8个成员占用1个字节；
9-16个成员占用2个字节；
17-24个成员占用3字节；
25-32个成员占用4个字节；
33-64个成员占用8字节。

MySQL/MariaDB限制最多只能有64个成员。

存储SET数据类型的数据时忽略重复成员并按照枚举时的顺序存储。如set('b','b','a')，存储'a,b,a','b,a,b'的结果都是'b,a'。

mysql> create table test8(a set('d','b','a'));
mysql> insert into test8 values('b,b,a'),('b,a,b'),('bab');
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 1mysql> select * from test8;
+-----+
| a   |
+-----+
| b,a |
| b,a |
|     |
+-----+3 rows in set

使用find_in_set(set_value,set_column_name)可以检索出包含指定set值set_value的行。例如检索a字段中包含成员b的行：

mysql> select * from test8 where find_in_set('b',a);
+-----+
| a   |
+-----+
| b,a |
| b,a |
+-----+2 rows in set

1.3 数据类型属性：unsigned

unsigned属性就是让数值类型的数据变得无符号化。使用unsigned属性将会改变数值数据类型的范围，例如tinyint类型带符号的范围是-128到127，而使用unsigned时范围将变成0到255。同时unsigned也会限制该列不能插入负数值。

create table t(a int unsigned,b int unsigned);insert into t select 1,2;insert into t select -1,-2;

上面的语句中，在执行第二条语句准备插入负数时将会报错，提示超出范围。

使用unsigned在某些情况下确有其作用，例如一般的ID主键列不会允许使用负数，它相当于实现了一个check约束。但是使用unsigned有时候也会出现些不可预料的问题：在进行数值运算时如果得到负数将会报错。例如上面的表t中，字段a和b都是无符号的列，且有一行a=1,b=2。

mysql> select * from t;
+---+---+
| a | b |
+---+---+
| 1 | 2 |
+---+---+1 row in set

此时如果计算a-b将会出错，不仅如此，只要是unsigned列参与计算并将得到负数都会出错。

mysql> select a-b from t;1690 - BIGINT UNSIGNED value is out of range in '(`test`.`t`.`a` - `test`.`t`.`b`)'mysql> select a-2 from t;1690 - BIGINT UNSIGNED value is out of range in '(`test`.`t`.`a` - 2)'

如果计算结果不是负数时将没有影响。

mysql> select 2-a,a*3 from t;
+-----+-----+
| 2-a | a*3 |
+-----+-----+
|   1 |   3 |
+-----+-----+1 row in set

这并不是MySQL/MariaDB中的bug，在C语言中的unsigned也一样有类似的问题。这个问题在MySQL/MariaDB中设置set sql_mode='no_unsigned_subtraction'即可解决。

所以个人建议不要使用unsigned属性修饰字段。

1.4 数据类型属性：zerofill

zerofill修饰字段后，不足字段显示部分将使用0来代替空格填充，启用zerofill后将自动设置unsigned。zerofill一般只在设置了列的显示宽度后一起使用。关于列的显示宽度在上文已经介绍过了。

mysql> create table t1(id int(4) zerofill);
mysql> select * from t1;
+-------+
| id    |
+-------+
|  0001 |
|  0002 |
|  0011 |
| 83838 |
+-------+4 rows in set (0.00 sec)

zerofill只是修饰显示结果，不会影响存储的数据值。

위 내용은 MySQL 데이터 유형 및 저장 메커니즘에 대한 자세한 설명의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!