MySQLDump issue with a special characters

innodbMySQLmysql-5.5mysqldumpwindows

Code:
use test;

CREATE TABLE Test_user(Username VARCHAR(15),path VARCHAR(100));

INSERT INTO Test_user VALUES('new1','C:\newfolder\one.xlsx');
INSERT INTO Test_user VALUES('new1','C:/newfolder/one.xlsx');

I have created a .sql file using MySQLDump, which has contents

Filename : Table1.sql

DROP TABLE IF EXISTS test_user;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE test_user (
Username varchar(15) DEFAULT NULL,
path varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/!40101 SET character_set_client = @saved_cs_client */;

LOCK TABLES test_user WRITE;
/*!40000 ALTER TABLE test_user DISABLE KEYS /;
INSERT INTO test_user VALUES ('new1','C:\newfolderone.xlsx'),('new1','C:/newfolder/one.xlsx');
/!40000 ALTER TABLE test_user ENABLE KEYS */;
UNLOCK TABLES;

in the above Table1.sql file ('new1','C:\newfolderone.xlsx') backslash before one.xlsx is not displaying , While trying to execute this file, i am getting output like this

new1 C:ewfolderone.xlsx
new2 C:/newfolder/one.xlsx

Where it is not displaying \ at all , suggest me how to resolve it and also what are special characters like this backslash will create this kind of bug.

Thanks in advance..

Best Answer

You should read about string literals in MySQL.

There you’ll learn that a backslash character is used to escape some special strings, and to have literal backslashes in your strings (as in Windows pathnames), you have to double them:

INSERT INTO test_user VALUES ('new1','C:\\newfolderone.xlsx');

The complete list of characters in need of a special treatment is at the above link.

However, it is best to have the intermediate language deal with such problems. For instance, in PHP you would call PDO::quote; other platforms and languages provide similar facilities.

Related Question