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

This article mainly introduces "what are the pits about mysql". In daily operation, I believe many people have doubts about the pits of mysql. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the questions about "what are the pits of mysql?" Next, please follow the editor to study!

I. Preface

For students engaged in Internet development, mysql is all too familiar. Whether it's DBA, development or testing, you basically have to deal with it every day, and many students may have experienced a lot of battles. However, I do not know if you have all experienced these holes that the author has encountered.

Signed and unsigned

In the past, when our company formulated the development specification at the beginning of the project development, we specifically discussed whether the int type field of mysql was defined as signed or unsigned.

Point one:

For fields that can be sure that the values stored in them must be positive, they are defined as UNSIGNED unsigned, which can save half of the storage space. The statement to create an unsigned field is as follows:

Create table test_unsigned (an int UNSIGNED, b int UNSIGNED)

Point two:

It is recommended to define signed fields, which are easy to use. The default int type of mysql is signed. The statements to create signed fields are as follows:

Create table test_signed (an int); insert into test_signed values (- 1)

Execution result:

Insert-1 in field a, and we see that it can be operated successfully.

For these two scenarios, after a heated discussion, we chose to use signed to define int type fields. Why?

Create table test_unsigned (an int UNSIGNED, b int UNSIGNED); insert into test_unsigned values (1penny 2)

First create the test_unsigned table, which contains two unsigned fields an and b, and then insert a piece of data

Select b-a from test_unsigned

No problem. Return 1.

But if the sql is changed to this:

Select a-b from test_unsigned

Execution result:

Wrong report.

Therefore, when using unsigned fields, be sure to pay attention to the fields subtracted from the negative number of the pit, it is recommended to use signed fields to avoid unnecessary problems.

III. Automatic growth

Students who have built a table know that the primary key for a table can be defined as automatically growing, so that it can be left to the database to generate its own primary key value without having to specify it in the code, and the generated value is incremental. In general, the statement to create a table is as follows:

Create table test_auto_increment (an int auto_increment primary key)

But what happens if it changes to this?

Create table test_auto_increment (an int auto_increment)

Execution result:

Wrong report.

Not all of it is shown in the screenshot, and the complete prompt looks like this:

1075-Incorrect table definition; there can be only one auto column and it must be defined as a key, Time: 0.006000

It means auto-growing field, which must be defined as key, so we need to add primary key.

In addition, there is an interesting experiment:

Insert into test_auto_increment (a) values (null), (50), (null), (8), (null)

Guess what the result of the implementation will be?

The first null is inserted by 1, then sorted by the real number size and then inserted, and the last two null are added to the largest number by 1.

And look at this sql primary key to insert a negative number, can it be executed successfully?

Insert into test_auto_increment values (- 3)

The answer is yes, the primary key can insert negative numbers.

And what about this sql? insert 0? in the primary key.

Insert into test_auto_increment values (0)

Execution result:

It can also be executed successfully, but no data is inserted

IV. Field length

When we create the table, after defining the type of the field, we need to specify the length of the field, such as varchar (20), biginit (20), and so on. So the question is, does varchar represent byte length or character length?

Create table test_varchar (a varchar (20)); insert into test_varchar values ('Su San Shuo Technology'); select length (a), CHARACTER_LENGTH (a) from test_varchar

The result after execution:

We can see that the length of the five-word length function in Chinese is 15, which means it takes up 15 bytes, while the statistical length of 5 using the charcter_length function represents 5 characters. So varchar represents character length, because some complex characters or Chinese characters cannot be represented by one byte, and a Chinese character in utf8 format takes up 3 bytes. The comparison table of different database encoding formats and different bytes occupied is as follows:

Mysql except that varchar and char represent character length, the other types represent byte length.

What does int (n) mean by this n? Starting from a column:

Create table test_bigint (a bigint (4) ZEROFILL); insert into test_bigint values (1); insert into test_bigint values (123456); select * from test_bigint

ZEROFILL indicates that the length is not enough to fill 0.

Execution result:

Mysql commonly used numeric type field occupancy byte comparison table:

You can see from the table that the actual length of bigint is 8 bytes, but the a we defined shows 4 bytes, so fill in 0 in front of it when it is less than 4 bytes. When it is full of 4 bytes, it is displayed according to the actual length, for example: 123456. However, it is important to note that some mysql clients may only display 4 bytes even if they are full of 4 bytes, such as 1234.

So bigint (4), where 4 means that the displayed length is 4 bytes, the actual length is still 8 bytes.

Ignore case

We know that there is a case problem in the English alphabet, such as: are an and A the same? We think it must be different, but how does the database handle it?

Create table test_a (a varchar (20)); insert into test_a values ('a'); insert into test_a values ('A'); select * from test_a where a ='a'

What is the result of the implementation?

I thought I would only return a, but I actually returned A, too. why?

The default Collation for this table is utf8_general_ci, and this Collation ignores case, so the value of the lowercase letter an is queried, and accidentally the value of the uppercase letter An is also queried.

So what if we just want to find out the value of lowercase a? Take a look at which Collation is supported by mysql?

Show collation

From the image above, we can find utf8_bin, which represents the data in binary format. Let's try it as a type.

Modify the field type

ALTER TABLE test_a MODIFY COLUMN a VARCHAR (20) BINARY CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL

Check the data again.

Select * from test_a where a ='a'

Execution result:

Sure enough, the result was right.

VI. Special characters

When I was working on the project, I provided a message function, and as a result, the client user entered an emoji expression, which directly led to the error of the interface.

The reason for the final location is that at that time, the character encoding of the database and table is a maximum of 3 bytes of a character encoded by utf8,mysql 's utf8, but an emoji expression is 4 bytes, so utf8 does not support storing emoji expressions.

How to solve this problem?

Changing character encoding to utf8mb4,utf8mb4 can have up to 4 bytes, but it is only supported in mysql5.5.3 or later.

Modify the configuration file my.cnf or my.ini configuration file of mysql as follows:

[client] default-character-set = utf8mb4 [mysqld] character-set-server = utf8mb4collation-server = utf8mb4_general_ci

Restart MySQL, and then use the following command to view the encoding, which should all be utf8mb4, which is the way to modify the encoding of the entire database.

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE' collation%';ji

The result is:

You can also modify the encoding of a table individually:

Alter table test_a convert to character set utf8mb4 collate utf8mb4_bin

And modify the encoding of a field:

ALTER TABLE test_a CHANGE an a VARCHAR (20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

In addition, it is recommended that students define the character encoding as utf8mb4 when creating databases and tables to avoid some unnecessary problems.

At this point, the study of "what are the pitfalls of mysql" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.