Mysql deletes a table with special characters in its name.

Due to the adjustment of the company's business and application, many tables in Mysql are not needed before, so we need to sort out the database.

At the beginning, I was wondering: Will some tables be deleted? A good solution. Just write a simple script. I have read more than 80000 tables in the database. Many tables are named by IP addresses, and these tables are the objects to be cleaned up.

So I use the following command to export all the table names to a file:

Mysql-uroot-p123456-A-e "use cdn; show tables;"> allDBName.txt

Run cat allDBName.txt | grep "^ [0-9] \ {1, 3 \} \. [0-9] \ {1, 3 \} \."> ipDB.txt to import the table name starting with an IP address to ipDB.txt. Use the following script to delete the file.

#! /Bin/bash
Cat ipDB.txt | while read DBname
Do
Mysql-uroot-p123456-A-e "use cdn; drop table if exists $ DBname;" 2> drop. err
If [$? -Ne 0]; then
Echo "Drop $ DBname failed"
Fi
Done

Exit 0

I executed the above script in the background and did not see whether the execution was correct. I personally felt that it was okay, so I did other things. However, after a period of time, I executed the following statements in the information_schema database to check the database size for hours. I found that the database size before the table deletion was not reduced, but increased (in the actual environment, running business ).

Select concat (round (sum (DATA_LENGTH/1024/1024), 2), 'mb') as datasize from TABLES where table_schema = 'cdn ';

Let's look at the directory of the script. There is an additional drop. err file, and it is all the same error:

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 ''192. 168.1.30 ''at line 1

I am wondering, why is an error reported? So I entered the database in the terminal and executed:
Drop table if exists '192. 168.1.30 ';
But I am confused that the same error is still reported. WHY? There is no syntax error! Later, I thought it was wrong: the points in the table name are not counted as special characters, which may be the problem. So I checked some materials and found the solution to this problem: Put the table name in the upper limit. Note that the upper limit is the character on the left of the Number 1 associated with the letter, I tried it and it was successful.

Therefore, I changed the $ DBname in the script to '$ dbname', but ZTE reported an error: command not found. It should be a double quotation mark, change the quotation marks to single quotation marks. The bold code is as follows:

#! /Bin/bash
Cat ipDB.txt | while read DBname
Do
Mysql-uroot-p123456-A-e 'use cdn; drop table if exists '$ dbname';' 2> drop. err
If [$? -Ne 0]; then
Echo "Drop $ DBname failed"
Fi
Done

Exit 0

Then run the command. No error is reported. The problem should be solved! I can check the number of targets, but it still hasn't changed. Why! No error was reported, but the table was not deleted. Let's take a closer look at the handling of special characters in the shell script, just change it to the following:

#! /Bin/bash
Cat ipDB.txt | while read DBname
Do
Mysql-uroot-p123456-A-e "use cdn; drop table if exists \ '$ DBname \';" 2> drop. err
If [$? -Ne 0]; then
Echo "Drop $ DBname failed"
Fi
Done
Exit 0

At this point, execute the command again. If no error is reported, check the number of tables again. It was a day after several symbols! Alas! Learn some lessons!