How MySQL evaluates if we use any other escape character rather that back-slash () in a text file while importing the data from text file to MySQL table?


Back-slash(\) is the by default escape character for the MySQL and when we use it in the text file then we do not need to mention it in the query while importing the data from text file to table. But if we use any other character as escape character then it must be mentioned by using ESCAPED BY option in the query while importing the text file into a table. It can be understood with the help of the following example −

Suppose we are using star symbol (‘* ‘) as the escape character in a text file as follows −

id,Name,Country,Salary
105,Chum*,Marsh,USA,11000
106,Danny*,Harrison,AUS,12000

Now while importing this text file into MySQL table then we need to mention ‘ESCAPED BY’ option in the query as follows −

mysql> LOAD DATA LOCAL INFILE 'd:\A.txt' INTO table employee6_tbl FIELDS TERMINATED BY ',' ESCAPED BY ‘*’ IGNORE 1 ROWS;
Query OK, 2 rows affected (0.03 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

Now, we can see what has been imported with the help of the following query −

mysql> Select * from employee6_tbl;
+------+----------------+----------+--------+
| Id   | Name           | Country  | Salary |
+------+----------------+----------+--------+
| 105  | Chum,Marsh     | USA      | 11000  |
| 106  | Danny,Harrison | AUS      | 12000  |
+------+----------------+----------+--------+
2 rows in set (0.00 sec)

Updated on: 04-Feb-2020

218 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements