Dumping and importing with backslash

Posted on

Question :

Background

I have a MySQL database, from which I run mysqldump for backups.

In this database, I have some fields that will contain some backslashes, and when dumping this, it will contain something like

INSERT INTO table1 (col1, col2) VALUES (1, 'frac{1}{2}');

(For reference, this is TeX, used in this case for typesetting mathematics.)

Problem

Now, when I import this backup with

mysql -u username -p database_name < file.sql

the backslash in the col2 value disappears. Likely, instead of inserting it, it’s being used to escape the following character.

Question

Is there a way to insert this while maintaining the backslash?

My idea so far is to make a script that plows through the file and replaces every occurrence of with \. Is this reliable?

I would assume that someone already thought of this problem and maybe created some way of dealing with it.

Any help appreciated!

Answer :

If you use mysqldump to export your data it will take care of this for you. For example:

mysql> select * from table1;
+------+-------------+
| id   | mystr       |
+------+-------------+
|    1 | frac{1}{2} |
+------+-------------+

mysqldump -uroot -p database1 table1 > table1.sql

cat table1.sql | grep VALUES
INSERT INTO `table1` VALUES (1,'\frac{1}{2}');

notice the escaped backslash? and:

mysql -uroot -p database1 < table1.sql

mysql -uroot -p database1 -e "select * from table1"
Enter password: 
+------+-------------+
| id   | mystr       |
+------+-------------+
|    1 | frac{1}{2} |
+------+-------------+

In what context did you run the INSERT? Most client languages have a way of “escaping what needs to be escaped for MySQL”. Such a function would take care of doubling up the backslash and dealing with quotes.

Leave a Reply

Your email address will not be published.