Detaillierte Erläuterung der MySQL-Datentypen und des Speichermechanismus
- 一个新手Original
- 2017-10-26 09:08:411661Durchsuche
Verzeichnis dieses Artikels:
1.1 Übersicht über Datentypen
1.2 Speichermechanismus und Betriebsmethode
1.2.1 Ganzzahlspeichermethode
1.2.2 Zeichentyp-Speichermethode
1.2.3 Datums- und Uhrzeittyp-Speichermethode
1.2.4 ENUM-Datentyp
1.2.5 SET-Datentyp
1.3. Datentypattribut: ohne Vorzeichen
1.3. Datentypattribut: Zerofill
1.1 Datentypübersicht
Datentyp ist eine Feldbeschränkung, die jedes Was einschränkt Welche Art von Daten können in jedem Feld gespeichert werden, wie viele Daten können gespeichert werden, in welchem Format sie gespeichert werden können usw. MySQL/MariaDB verfügt grob über fünf Arten von Datentypen, nämlich: Ganzzahl, Gleitkomma, Zeichenfolgentyp, Datums- und Uhrzeittyp sowie spezielle ENUM- und SET-Typen.
Die Bedeutung, Einschränkungen und zugehörigen Anweisungen dieser fünf Datentypen sind in der folgenden Abbildung dargestellt:
Die Anzahl der von jedem Datentyp belegten Bytes, siehe offizielles Mariadb-Handbuch.
1.2 Speichermechanismus und Betriebsmethode
Der Grund, warum der Datentyp die Datenspeicherlänge des Felds begrenzen kann, liegt darin, dass der Adressraum und die Länge des Adressraums im Adressraum streng abgegrenzt sind Speicher, wenn die Tabelle erstellt wird. Wie viele Bytes an Daten können gespeichert werden? Dies ist natürlich ein sehr grobes Konzept. Weitere spezifische Speichermethoden finden Sie in der Beschreibung unten.
Es gibt zwei Möglichkeiten, den Bereich der Datentypen einzuschränken: Eine besteht darin, den Speicherplatz streng zu begrenzen und nur so viele Daten zu speichern, wie der Speicherplatz aufgeteilt ist Überschüssige Daten werden abgeschnitten. Die andere besteht darin, zusätzliche Daten zu verwenden. Das Bit des Bytes wird verwendet, um zu markieren, ob das Byte in einem bestimmten Adressraum gespeichert ist wird nicht markiert.
1.2.1 Speichermethode des Ganzzahltyps
Hier erklären wir hauptsächlich die Speichermethode des Ganzzahltyps. Was die Speichermethode des Gleitkomma-Datentyps betrifft, gibt es zu viele Dinge, um viele zu berücksichtigen.
Für ganzzahlige Datentypen ist der Speicherplatz streng begrenzt, aber es unterscheidet sich von Zeichen, da die 0 und 1 der Bits in jedem geteilten Byte den Wert direkt berechnen können, sodass der Bereich basierend auf berechnet wird die Anzahl der Bits. Ein Byte hat 8 Bits, also 2^8=256 Werte. Ein 4-Byte-Int belegt 32 Bits und kann 0 sein -2^32. Mit anderen Worten: Zahlen zwischen 0 und 255 belegen nur ein Byte und Zahlen zwischen 256 und 65535 erfordern zwei Bytes.
Es ist zu beachten, dass der Integer-Datentyp in MySQL/Mariadb den Parameter M verwenden kann. M ist eine positive Ganzzahl, wie z. B. INT(M), tinyint(M). Dieses M stellt die Anzeigelänge dar. Int(4) bedeutet beispielsweise, dass bei der Ausgabe eine 4-stellige Ganzzahl angezeigt wird. Wenn die Anzahl der Ziffern im tatsächlichen Wert kleiner als die Anzeigewertbreite ist, werden Leerzeichen zum Auffüllen verwendet standardmäßig auf der linken Seite. Wenn die Anzahl der Ergebnisziffern den Grenzwert überschreitet, wird das Anzeigeergebnis nicht beeinflusst. Im Allgemeinen wird diese Funktion mit dem Zerofill-Attribut verwendet, um Leerzeichen mit 0 zu füllen, aber nach der Verwendung von Zerofill wird die Spalte automatisch zu einem vorzeichenlosen Feld. Zum Beispiel:
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)
Das Einzige, was zu beachten ist, ist, dass die Anzeigebreite nur den Anzeigeeffekt beeinflusst und keine Vorgänge wie Speicherung, Vergleich, Längenberechnung usw. beeinflusst . .
1.2.2 Speichermethoden für Zeichentypen
Hier erklären wir hauptsächlich die Speichermethoden und Unterschiede zwischen char und varchar.
Der char-Typ wird oft als „String-Typ mit fester Länge“ bezeichnet. Er begrenzt die Leerzeichenlänge streng, aber er begrenzt die Anzahl der Zeichen, nicht die Anzahl der Bytes, sondern sie Früher war die Version auf die Anzahl der Bytes begrenzt. Daher speichert char(M) ausschließlich M Zeichen, und der nicht ausreichende Teil wird mit Leerzeichen aufgefüllt, und der Teil, der mehr als M Zeichen enthält, wird direkt abgeschnitten.
Da der char-Typ die Fähigkeit hat, ihn beim Abrufen von Daten mit Leerzeichen zu füllen, um die Authentizität der Daten widerzuspiegeln aus dem Adressraum, nachfolgende Leerzeichen automatisch entfernen. Dies ist eine Besonderheit von char. Sogar die abschließenden Leerzeichen, die wir manuell speichern, werden als automatisch vervollständigt betrachtet und beim Abrufen gelöscht. Das heißt, die Ergebnisse von name='gaoxiaofang ' und name='gaoxiaofang' in der where-Anweisung sind die gleichen.
Zum Beispiel:
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
Wie Sie den obigen Ergebnissen entnehmen können, kann char(4) nur 4 Zeichen speichern und nachfolgende Leerzeichen werden entfernt.
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只是修饰显示结果，不会影响存储的数据值。
Das obige ist der detaillierte Inhalt vonDetaillierte Erläuterung der MySQL-Datentypen und des Speichermechanismus. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!