Shulou(Shulou.com)05/31 Report--

MYSQL's CHAR and VARCHAR precautions and how binary and varbinary are stored, this article introduces in detail the corresponding analysis and solutions, hoping to help more partners who want to solve this problem to find a more simple and easy way.

Version 5. 7 storage engine INNODB line format Dynamic

Conceptually, they are used to store character data, and the range allowed is:

1. Char 0-255bytes, fixed length, insufficient length is made up by ASCII 0X20.

2. Varchar 0-65535 (2 ^ 8-1) bytes. Note that it is the length of all the varchar fields in the entire table. The so-called variable length is to allocate space as needed.

The following issues are discussed:

The first problem: variable length of varchar

So this leads to the first question, variable length, in INNODB (Dynamic line format), in the header, 1-2 bytes.

To store this variable length and:

Variable field lengths (1-2 bytes* var)

(for more information, please see http://blog.itpub.net/7728585/viewspace-2071787/)

2 bytes is exactly 65535 in length, which is an implementation of MYSQL by INNODB, and if you use 5.7 INNODB

The length of online DDL modify varchar column can be done quickly between 1-255and 256-65535, but

If you span, for example, change the length of a varchar field from 250 to 300 bytes, you will need to use the

Methods such as inpace or copy, which is very slow, is also here because it involves the expansion of the wardrobe, which used to be a

Bytes to store the length, and after the change requires two bytes, of course, you need to reorganize the table, and if you don't span it, you don't.

Will change the organization of the table, that is, the value needs to modify the data dictionary and frm file, of course, in an instant, let's do the following

A test. For the UTF8 character set, this point is 255Universe 85.

Note that using the version 5.7 engine for innodb row format is Dynamic, and this column cannot have an index, if any

The index will bring in and out operations, and it is also relatively slow.

Mysql > select count (*) from testshared3

+-+

| | count (*) |

+-+

| | 1048576 |

+-+

1 row in set (0.35 sec)

Mysql > show create table testshared3

+- -+

| | Table | Create Table |

+- -+

| | testshared3 | CREATE TABLE `testshared3` (

`id`int (11) DEFAULT NULL

`name` varchar (13) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+- -+

1 row in set (0.01 sec)

Mysql > alter table testshared3 change name name varchar (85)

Query OK, 0 rows affected (0.07 sec)

Records: 0 Duplicates: 0 Warnings: 0

You can see that although there are 1048576 rows of data, the modify is completed in an instant. But what if you change it from 85 to 86?

Mysql > alter table testshared3 ALGORITHM=INPLACE, change name name varchar (86)

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

Mysql > alter table testshared3 change name name varchar (86)

Query OK, 1048576 rows affected (15.68 sec)

Records: 1048576 Duplicates: 0 Warnings: 0

You can see that it was used for more than 15 seconds, and ALGORITHM=COPY.

Second question: about the differences in storage and display of left spaces between char and varchar

Mysql > create table testvc (name1 varchar (5), name2 char (5))

Query OK, 0 rows affected (0.08 sec)

Mysql > insert into testvc values ('gao', 'gao')

Query OK, 1 row affected (0.01sec)

Mysql > select concat (name1,')'), concat (name2,')') from testvc

+-+ +

| | concat (name1,')') | concat (name2,')') |

+-+ +

| | gao) | gao) |

+-+ +

1 row in set (0.06 sec)

You can see that varchar can normally display the spaces behind gao, but char is not, so how are they stored internally? we need

Look at it in binary mode:

(the following is what I have parsed, and I need to use myself for the specific method reference http://blog.itpub.net/7728585/viewspace-2071787/.

Write a few gadgets)

04-varchar length

00-NULL bitmap

00

0010

0024

00000089a25

40000002e0c1bd9

000001640110

67616f20-- varchar (5) 'gao'

67616f2020-- char (5) 'gao'

Here we can clearly see that the length of the varchar is 4. When storing the 'gao' of the varchar, it stores the 'gao' of the 0X67616f20, that is, the 'gao' of the ASCII, and when storing the 'gao' of the char type

When it is 0X67616f2020, you can see that there are two 0X20 behind him, that is, the space of ASCII, so we can know that char (5) will add ASCII 0X20 to all the insufficient bytes, that is,

Why the output space is gone, because the trailing 0X20 is used as a complementary character when it is stored in the char field, but this is not the case in VARCHAR. 0X20 is the actual character, that is,

If it's a space, then the output will be there.

The third problem: compare the spaces with the trails of varchar and char.

Described in the MYSQL document:

This means that all CHAR, VARCHAR, and TEXT values in MySQL are compared without regard to any trailing spaces

"Comparison" in this context does not include the LIKEpattern-matching operator

For those cases where trailing pad characters are stripped or comparisons ignore them, if a column has

An index that requires unique values, inserting into the column values that differ only in number of trailing

Pad characters will result in a duplicate-key error.

That is, except for the comparison operation of LIKE, trailing spaces, whether VARCHAR CHAR or TEXT, are ignored, and if the field is a unique key, uniqueness is judged.

Spaces are also ignored.

Or the table just now. We added a unique index to the name1 of varchar.

Mysql > alter table testvc add unique key (name1)

Query OK, 0 rows affected (0.07 sec)

Records: 0 Duplicates: 0 Warnings: 0

The data we just inserted is

Insert into testvc values ('gao', 'gao')

Mysql > select * from testvc where name1='gao'

+-+ +

| | name1 | name2 |

+-+ +

| | gao | gao |

+-+ +

1 row in set (0.00 sec)

Mysql > select * from testvc where name1='gao'

+-+ +

| | name1 | name2 |

+-+ +

| | gao | gao |

+-+ +

1 row in set (0.00 sec)

You can see that no matter whether there is a 0X20 space in the storage, or whether the condition = is followed by a 0X20 space or not

All will be queried. Let's test the insertion.

Mysql > insert into testvc values ('gao','gao10')

ERROR 1062 (23000): Duplicate entry 'gao' for key' name1'

Mysql > insert into testvc values ('gao', 'gao10')

ERROR 1062 (23000): Duplicate entry 'gao' for key 'name1'

Whether I insert 'gao'' or 'gao'' is a duplicate value, proving the documentation statement, in addition

This problem does not exist in ORACLE, and MYSQL is rather strange. Many of the concepts of ORACLE are in MYSQL

Need to put a question mark in.

ORACLE:

SQL > create table testui1 (name varchar2 (20))

Table created

SQL > create unique index testuiq_IDX on testui1 (name)

Index created

SQL > insert into testui1 values ('gao')

1 row inserted

SQL > insert into testui1 values ('gao')

1 row inserted

SQL > insert into testui1 values ('gao')

1 row inserted

SQL > commit

Commit complete

Let's take a look at LIKE:

Varchar:

Mysql > select * from testvc where name1 like 'gao%'

+-+ +

| | name1 | name2 |

+-+ +

| | gao | gao |

+-+ +

1 row in set (0.00 sec)

Mysql > select * from testvc where name1='gao'

+-+ +

| | name1 | name2 |

+-+ +

| | gao | gao |

+-+ +

1 row in set (0.00 sec)

Char:

Mysql > select * from testvc where name2 like 'gao%'

Empty set (0.00 sec)

Mysql > select * from testvc where name2='gao'

+-+ +

| | name1 | name2 |

+-+ +

| | gao | gao |

+-+ +

1 row in set (0.00 sec)

Here char name2 like 'gao%' has no data, while varchar name1 like 'gao%'

The data also proves our analysis of the storage format, because the 0X20 space at the end of the char is in

Storage has been removed, but VARCHAR does not, as long as the LIKE is a strict match will come out like this

Effect.

Finally, let's take a look at MYSQL's binary and varbinary formats, which means that both storage and comparison use a binary format, that is, according to a

The comparison ASCII value of a byte, which is officially described as follows:

They contain byte strings rather than character strings. This

Means that they have no character set, and sorting and comparison are based on the numeric values of

The bytes in the values.

Their meaning is similar to that of char and varchar, but for one thing, the comparison method is not the same as the storage method.

Binary uses 0X00, that is, bytes that are not enough to make up, and the comparison is made strictly with the format in storage.

Matching does not have the same treatment of spaces as char and varchar.

Mysql > insert into testbin3 values ('a','a')

Query OK, 1 row affected (0.03 sec)

Mysql > desc testbin3

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | name1 | varbinary (10) | YES | | NULL |

| | name2 | binary (10) | YES | | NULL |

+-+ +

2 rows in set (0.00 sec)

Mysql > select * from testbin3 where name1='a'

+-+ +

| | name1 | name2 |

+-+ +

| | a | a |

+-+ +

1 row in set (0.00 sec)

Mysql > select * from testbin3 where name2='a'

Empty set (0.00 sec)

Mysql > select * from testbin3 where name2='a\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0'

+-+ +

| | name1 | name2 |

+-+ +

| | a | a |

+-+ +

1 row in set (0.00 sec)

You can see that varbinary can query records using'a', but binary can't find records using'a'. Why?

Let's look at his internal storage.

00000089a25f

0000002e0c66bc

0000012a0110

6120-binary'a'

612000000000000000-- varbinary'a'

You can see that varbinary uses 8 0X00 to complement. Since he matches strictly according to the rules, then we can do this.

Find out the data:

Mysql > select * from testbin3 where name2='a\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0'

+-+ +

| | name1 | name2 |

+-+ +

| | a | a |

+-+ +

1 row in set (0.00 sec)

Of course, unique also makes comparisons in strict accordance with each other.

Add a unique key to binary

Mysql > alter table testbin3 add unique key (name2)

Mysql > insert into testbin3 values ('a','a\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0')

ERROR 1062 (23000): Duplicate entry'a 'for key' name2'

You can see the duplicate lines.

The answers to the questions about MYSQL's CHAR and VARCHAR notes and how binary and varbinary are stored are shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.