Question: When the short message is audited, according to the content of the judge, such as a content can be passed, B content could not pass, then the MySQL in the performance of msg = A,msg = B, but if the contents of the MSG field contains a carriage return line, such as white space characters (the most common is the beginning of the content and the end of the line Then the comparison is unsuccessful, resulting in short message audit unsuccessful.
Because there is no way to completely filter the content when it is inserted, so audit here often problems, make the head big, and MySQL trim function can not get rid of the return and line, only to remove the extra space, today studied the MySQL function, replace function, finally solved the problem, Specific solutions are as follows:
Suppose you want to audit the contents of the database as "I love you.
"SMS (Note that there is a line change after the content) through (status change to 1)
The previous SQL statement is not working
Copy Code code as follows:
UPDATE ' Tran '
SET ' status ' = ' 1 '
WHERE ' msg ' = ' I love you ';
The statement after the modification
Copy Code code as follows:
UPDATE ' Tran '
SET ' status ' = ' 1 '
WHERE Trim (replace (' msg ', ' \ r \ n ', ')) = ' I love you ';
The data in the carriage return line replacement and then trim off, to achieve the purpose, although not particularly perfect, but because there is no way in the user input control, so can only make this unwise, but the efficiency of MySQL built-in function is very guaranteed.
Update (15:50):
Copy Code code as follows:
UPDATE ' Tran '
SET ' status ' = ' 1 '
WHERE Trim (Trim (
BOTH ' \ r \ n '
From content) = ' I love You '
Using two trim, the advantage is not to replace the middle of the line and carriage return, will only handle the trailing space-line carriage return, the equivalent of PHP in the role of the trim function.
Attached: Replace usage
1.replace into
Copy Code code as follows:
Replace into table (Id,name) VALUES (' 1 ', ' A '), (' 2 ', ' B ');
The purpose of this statement is to insert two records into table tables.
2.replace (object, Search,replace)
Replaces all occurrences of search in object with replace
Select replace (' www.qq.com ', ' Q ', ' Y ');
The above example outputs the result www.YY.com
Note: For detailed usage of the trim and replace functions, please check the MySQL manual.
Author: Yue Long essays
