Shulou(Shulou.com)06/01 Report--

Foreword:

I wrote an article about the int type, and I always wanted to write an article about the string field type, but I couldn't figure out how to do it. Recently, I have paid more attention to the articles in this area, and decided to put an end to the articles that have been dragging on for a long time. This article will mainly introduce the usage and difference of string types char and varchar.

The experimental environment of this paper is MySQL version 5.7.23, the storage engine is Innodb,sql_mode in strict mode, and the character set is utf8.

Introduction to ▍ 1.CHAR types

When we use the char type to define a field, we often specify its length M, that is, char (M). In fact, M refers to the number of characters, that is, the maximum number of characters stored in this field, M can not specify, the default is 1, the range is [0255], a single letter, number, Chinese, and so on all occupy one character. The next Chinese character in the utf8 character set occupies 3 bytes. Let's briefly test it:

# suppose you create a test table CREATE TABLE `col1` (`col1` char DEFAULT NULL, `col2` char (5) DEFAULT NULL, `col3` char (10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 with the following table-building statement # enter the database to query and build the table statement as follows: char (M) M may not be specified. The default is 1mysql > show create table char_tb1\ gateway * 1. Row * * Table: char_tb1Create Table: CREATE TABLE `char_ tb1` (`col1` char (1) DEFAULT NULL, `col2` char (5) DEFAULT NULL `col3` char (10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec) # insert data you can see that M represents the maximum number of characters saved Letters, numbers, Chinese, etc., all occupy one character mysql > insert into char_tb1 (col1) values ('a'), ('1'), ('Wang'), (']') Query OK, 4 rows affected (0.01sec) mysql > insert into char_tb1 (col1) values ('aa'), (' 12'); ERROR 1406 (22001): Data too long for column 'col1' at row 1mysql > select * from char_tb1 +-+ | col1 | col2 | col3 | a | NULL | NULL | | 1 | NULL | NULL | | Wang | NULL | NULL |] | NULL | NULL | +-+ 4 rows in set (0.00 sec) mysql > insert into char_tb1 (col2) values ('abcd'), (' Wang-123') ('^ * 123'), ('123'), (' 12345') Query OK, 5 rows affected (0.01sec) mysql > insert into char_tb1 (col2) values ('abcdef'); ERROR 1406 (22001): Data too long for column' col2' at row 1mysql > select * from char_tb1 +-+ | col1 | col2 | col3 | +-+ | a | NULL | NULL | | 1 | NULL | NULL | | Wang | NULL | NULL |] | NULL | | NULL | | NULL | abcd | NULL | | NULL | Wang-123 | NULL | | NULL | ^ * 123 | NULL | NULL | 12 | NULL | | NULL | 12345 | NULL | +-+ 9 rows in set (0.00 sec) # the range of M found in the following test is [0255] mysql > alter table char_tb1 add column col4 char (0) Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > alter table char_tb1 add column col5 char; Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > alter table char_tb1 add column col5 char (256); ERROR 1074 (42000): Column length too big for column 'col5' (max = 255); use BLOB or TEXT instead ▍ 2.VARCHAR type introduction

Similarly, the M in varchar (M) represents the maximum number of characters saved, and a single letter, number, Chinese, and so on all occupy one character. The length range that varchar can store is 0-65535 bytes. In addition, varchar needs to record the length of a string with 1 or 2 extra bytes: if the maximum length of the column is less than or equal to 65535 bytes, only 1 byte is used, otherwise 2 bytes are used. For the Innodb engine, the utf8 character set, a single Chinese character occupies 3 bytes, so the maximum M in varchar (M) is no more than 21845, that is, the range of M is [0mem21845), and M must be specified. In addition, MySQL stipulates that the length of a single field is no more than 65535 bytes, and the maximum limit for a single line is 65535. TEXT and BLOB fields are not included here. That is, the sum of the lengths defined in all varchar fields in a single table cannot be greater than 65535, so not all varchar (M) fields can take 21844, so let's verify it:

# assume that the test table CREATE TABLE `varchar_ tb1` (`col1` varchar (0) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 is created with the following table statement # check the addition of fields in the table creation statement and find that M must specify mysql > show create table varchar_tb1\ Graph * 1. Row * * Table: varchar_tb1Create Table: CREATE TABLE `varchar_ tb1` (`col1` varchar (0) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0) .00 sec) mysql > alter table varchar_tb1 add column col2 varchar ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near''at line 'the following tests show that the maximum value of M is 21844mysql > CREATE TABLE `varchar_ tb2` (col1 varchar (21844)); Query OK, 0 rows affected (0.04sec) mysql > CREATE TABLE `varchar_ tb3` (col1 varchar (218445)); ERROR 1074 (42000): Column length too big for column' col1' (max = 21845) The following tests of use BLOB or TEXT instead# prove that the maximum limit of a single line is 65535 bytes mysql > CREATE TABLE `varchar_ tb3` (col1 varchar (10)); Query OK, 0 rows affected (0.04sec) mysql > alter table varchar_tb3 add column col2 varchar (21844); ERROR 1118 (42000): 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 BLOBsmysql > alter table varchar_tb3 add column col2 varchar (21834); ERROR 1118 (42000): 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 BLOBsmysql > alter table varchar_tb3 add column col2 varchar (21833) Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > show create table varchar_tb3\ Graph * 1. Row * * Table: varchar_tb3Create Table: CREATE TABLE `varchar_ tb3` (`col1` varchar (10) DEFAULT NULL `col2` varchar (21833) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec) ▍ 3.CHAR and VARCHAR comparison

The CHAR type is of fixed length, and MySQL always allocates enough space based on the defined string length. When the Char value is saved, fill in the spaces to the right of them to reach the specified length, and when the Char value is retrieved, the trailing space is deleted.

The VARCHAR type is used to store variable-length strings and does not fill in spaces if the characters do not reach the defined number of digits. However, because the row is longer, it may make the row longer than it used to be in UPDATE, which results in extra work. If a row takes up more space and there is no more space to store in the page, in this case InnoDB needs to split the page so that the row can be put into the page, which increases fragmentation.

The following briefly summarizes the applicable scenarios of CHAR and VARCHAR field types:

CHAR is suitable for storing very short strings, or all values are close to the same length. For example, CHAR is ideal for storing the MD5 value of a password because it is a fixed-length value. CHAR is also better than VARCHAR for data that changes frequently, because fixed-length CHAR types are not prone to fragmentation. For very short columns, CHAR is also more efficient in storage space than VARCHAR. For example, using CHAR (1) to store only Y and N values requires only one byte if you use a single-byte character set, but VARCHAR (1) requires two bytes because there is an extra byte of record length.

VARCHAR is appropriate in the following situations: the string is very long or the length of the string to be stored varies widely; the maximum length of the string column is much larger than the average length; and the column is rarely updated, so fragmentation is not a problem.

As an additional note, when defining the maximum length of a field, we should allocate it as needed and make an estimate in advance. Especially for the VARCHAR field, some people think that anyway, the VARCHAR data type allocates the length according to the actual needs, so it is better to give it a little larger. But this is not the case, for example, now we need to store an address information. According to the evaluation, we can use VARCHAR or VARCHAR to store 90-character data. Although they are used to store 90-character data, the storage space is the same, but the memory consumption is different. Longer columns consume more memory because MySQL usually allocates fixed-size blocks of memory to hold internal values, especially when arranging or manipulating using memory temporary tables. So we still can't be too generous when assigning VARCHAR data types. You still want to evaluate the actual required length, and then select the longest field to set the character length. If you consider redundancy, you can leave about 10% of the character length. Never assume that VARCHAR allocates storage space according to the actual length, and allocates length at will, or simply uses the maximum character length.

Summary:

This paper introduces the use of CHAR and VARCHAR field types respectively, and gives the comparison between the two and the applicable scenarios. In the actual production situation, we need a specific analysis of the specific situation, the appropriate is the best, I hope this article can give you reference.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.