Because there is no way to completely filter the content during insertion, there are often problems during review. This leads to a huge increase, while the MySQL trim function cannot remove the carriage return and line feed, and only remove unnecessary spaces.
Because there is no way to completely filter the content during insertion, there are often problems during review. This leads to a huge increase, while the MySQL trim function cannot remove the carriage return and line feed, and only remove unnecessary spaces.
Problem: when the short message is reviewed, it will be determined based on the content. For example, if the content of a can pass, and the content of B cannot pass, MySQL displays msg = a, msg = B, however, if the content of the msg field contains blank characters such as carriage return and line breaks (the most common is that line breaks appear at the beginning and end of the content), the comparison is unsuccessful, resulting in an unsuccessful short message review.
Because there is no way to completely filter the content during insertion, there are often problems during review. This leads to a huge increase, while the MySQL trim function cannot remove carriage returns and line breaks, and only remove unnecessary spaces, I have studied MySQL functions and replace functions, and finally solved this problem. The specific solution is as follows:
Assume that you want to review the database content as "I love you"
(Note that there is a line break after the content) through (status changed to 1)
Previous SQL statements do not work.
The Code is as follows:
UPDATE 'tran'
SET 'status' = '1'
WHERE 'msg '=' I love you ';
Modified statement
The Code is as follows:
UPDATE 'tran'
SET 'status' = '1'
WHERE trim (replace ('msg ',' \ r \ n', '') = 'I love you ';
If you replace the carriage return and line feed in the data with spaces and trim them out, the goal is achieved. Although it is not perfect, it cannot be controlled during user input, fortunately, the efficiency of MySQL built-in functions is quite guaranteed.
Update ):
The Code is as follows:
UPDATE 'tran'
SET 'status' = '1'
WHERE trim (
BOTH '\ r \ N'
FROM content) = 'Love you'
With two trim functions, the advantage is that it will not replace the line breaks and carriage returns in the middle of the content, but will only handle the spaces at the beginning and end of the line breaks, which is equivalent to the trim function in php.
Appendix: replace usage
1. replace
The Code is as follows:
Replace into table (id, name) values ('1', 'A'), ('2', 'B ');
This statement inserts two records into the table.
2. replace (object, search, replace)
Replace all search objects with replace
Select replace ('www .qq.com ', 'Q', 'y ');
The output result of the previous example is www.YY.com.
Note: For detailed usage of trim and replace functions, see the MySQL manual.
By Yue long Miscellaneous
