There are many special symbols in MySQL that are quite disgusting. For example, there are single quotation marks ('), double quotation marks ("), backslashes (\) and so on in the string. Students can make up for the possible problems first?

In our usual SQL operations, if you don't pay attention to these symbols, you will be given a pot on your back.

Don't believe it. Listen to a word of advice. The water here is very deep. You can't grasp some things... The turn of Pan GA

Backslash (\) in INSERT statement

1. Actual test

Let's use the following SQL to test what the backslash (\) will look like in the INSERT statement?

INSERT INTO `demo0526` (`id`, `text`) VALUES (null, 'D:\Ha ha Chen\work overtime');
INSERT INTO `demo0526` (`id`, `text`) VALUES (null, 'D:\\Ha ha Chen\\work overtime');
INSERT INTO `demo0526` (`id`, `text`) VALUES (null, 'D:\\\Ha ha Chen\\\work overtime');
INSERT INTO `demo0526` (`id`, `text`) VALUES (null, 'D:\\\\Ha ha Chen\\\\work overtime');
INSERT INTO `demo0526` (`id`, `text`) VALUES (null, 'D:\\\\\Ha ha Chen\\\\\work overtime');

Results after insertion:

mysql> select * from demo0526;
+----+-----------------------+
| id | text                  |
+----+-----------------------+
|  1 | D:Chen haha works overtime          |
|  2 | D:\Ha ha Chen\work overtime        |
|  3 | D:\Ha ha Chen\work overtime        |
|  4 | D:\\Ha ha Chen\\work overtime      |
|  5 | D:\\Ha ha Chen\\work overtime      |
+----+-----------------------+
5 rows in set (0.00 sec)

We found the following results:

When there is 1 backslash in the string, 0 will be counted after insertion.

When there are two backslashes in the string, one is counted after insertion.

When there are three backslashes in the string, one is counted after insertion.

When there are 4 backslashes in the string, 2 are counted after insertion.

When there are 5 backslashes in the string, 2 are counted after insertion.

2. What is the principle?

Later, it was found that the reason is very simple. In MySQL, the backslash is an escape character in the string, and will be escaped once when parsed by the syntax parser. Therefore, when we insert the backslash (\) character, such as insert "\" in the database, only "" will be stored in the end, and the first backslash (\) will be treated as an escape character.

Similarly, when the syntax parser parses the D: \ \ Chen ha \ \ overtime string to the third backslash (\), it will process it as the next escape character. Therefore, D: \ \ Chen ha \ \ overtime becomes D: \ Chen ha \ \ overtime after it is warehoused.

Therefore, when we encounter backslashes when processing insert statements in the code, pay attention to whether \ has been changed to \, otherwise the string will be inconsistent after warehousing.

SELECT query backslash (\)

1. Actual test

We still use the above table data and directly test it with like fuzzy matching.

mysql> select * from demo0526;
+----+-----------------------+
| id | text                  |
+----+-----------------------+
|  1 | D:Chen haha works overtime          |
|  2 | D:\Ha ha Chen\work overtime        |
|  3 | D:\Ha ha Chen\work overtime        |
|  4 | D:\\Ha ha Chen\\work overtime      |
|  5 | D:\\Ha ha Chen\\work overtime      |
+----+-----------------------+
5 rows in set (0.00 sec)

Let's use a single backslash and two backslashes to see what we can find

mysql> SELECT * from demo0526 where text like '%\%';
Empty set (0.00 sec)

mysql> SELECT * from demo0526 where text like '%\\%';
Empty set (0.00 sec)

Ah!! After using like '%%' and like '% \%', we found that we couldn't find the data. Nani? Did I learn the above in vain?

Don't worry, I'll tell you, do the four backslashes (\ \) in the SELECT statement represent one? Ah, I'll try like '% \ \%'.

mysql> SELECT * from demo0526 where text like '%\\\\%';
+----+-----------------------+
| id | text                  |
+----+-----------------------+
|  2 | D:\Ha ha Chen\work overtime        |
|  3 | D:\Ha ha Chen\work overtime        |
|  4 | D:\\Ha ha Chen\\work overtime      |
|  5 | D:\\Ha ha Chen\\work overtime      |
+----+-----------------------+
4 rows in set (0.00 sec)

Oh? If I want to query the data with two backslashes (\) in the table, don't I want to like eight.... Don't stop me. I'll see who tm designed this rule.

mysql> SELECT * from demo0526 where text like '%\\\\\\\\%';
+----+-----------------------+
| id | text                  |
+----+-----------------------+
|  4 | D:\\Ha ha Chen\\work overtime      |
|  5 | D:\\Ha ha Chen\\work overtime      |
+----+-----------------------+
2 rows in set (0.00 sec)

2. What is the principle?

It turns out that in mysql's like syntax, the string behind like will be escaped once during syntax parsing and the second time during regular matching. Therefore, if you want to finally match "", you have to reverse escape twice, that is, from "" \ \ "" to "".

If it is an ordinary exact query (=), there is no need for a second regular escape, just like the INSERT statement.

mysql> SELECT * from demo0526 where text = '\\\\';
+----+------+
| id | text |
+----+------+
|  7 | \\   |
+----+------+
1 row in set (0.00 sec)

summary

Well, how do the students feel when they see here? Did you find that there are still many inconvenient grammars in MySQL, and there is still a lot of room for improvement~~

In daily work, we often encounter problems caused by this symbol, especially the data filled in by the user in the interface. It is suggested to make relevant restrictions to clarify which symbols are not allowed.