ERROR 1064 (42000): You have an error in your SQL syntax;

created at 11-09-2021 views: 6

Execute an sql statement in mysql today and report an error:

mysql> select sum(xxxx) from xxxx where dm='dbm' and DATE_FORMAT(sj,'%Y-%m') = DATE_FORMAT('sj$','%Y-%m');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select sum(xxxx) from xxxxx where dm='dbm' and DAT' at line 1

The reported error is a bit incomprehensible. Check the sql carefully and there is no grammatical error, so I typed it by myself and found that it can be executed correctly.

So, I put the error report and the hand typed in the txt, no difference was found, suspected that there are invisible characters, copied to the linux, checked with cat -A, and found the problem:

# cat -A a.txt
selectM-BM- sum(xxxxx) fromM-BM- xxxxxxx-BM- whereM-BM-
select sum(xxxxx) from formtable_main_125 where

I found that the sql above has some more M-BM- characters.

How to replace it? Use hexadecimal:

# cat -A b.txt
selectM-BM- sum(xxxxx)
# od -tcx1 b.txt
0000000   s   e   l   e   c   t 302 240   s   u   m   (   x   x   x   x
         73  65  6c  65  63  74  c2  a0  73  75  6d  28  78  78  78  78


solution

You can use the sed command to process the file, the command is as follows:
sed's/\xc2\xa0/ /g' -i file path
You can replace M-BM- with a space.

created at:11-09-2021
edited at: 11-09-2021: