<cut>
>> Hi!
>>
>> If you use mysqldump with --tab, tab characters should be escaped by
>> the the MySQL server. If you don't use --tab, then tabs shouldn't
>> need to be escaped.
>>
>> Can either of you send me a complete test that doesn't work?
>>
>> Regards,
>> Monty
Christian> Hi Monty
Christian> On a Win NT 4.0 SP4 I used this tabledefinition and data in database
> 'test':
Christian> CREATE TABLE bin (
Christian> extra_chars tinyblob,
Christian> id tinyint(4)
Christian> );
Christian> INSERT INTO bin (extra_chars, id) VALUES ('abcdefg',0);
Christian> INSERT INTO bin (extra_chars, id) VALUES ('abc\'defg',1);
Christian> INSERT INTO bin (extra_chars, id) VALUES ('abc\"defg',2);
Christian> INSERT INTO bin (extra_chars, id) VALUES ('abc\\defg',3);
Christian> INSERT INTO bin (extra_chars, id) VALUES ('abc\0defg',4);
Christian> INSERT INTO bin (extra_chars, id) VALUES ('abc\ndefg',5);
Christian> INSERT INTO bin (extra_chars, id) VALUES ('abc\tdefg',6);
Christian> Using mysqldump like this:
Christian> C:\Mack>mysqldump --tab=sql test bin
Christian> Produces the correct tabledefinition in 'sql\bin.sql':
Christian> # MySQL dump 5.13
Christian> #
Christian> # Host: localhost Database: test
Christian> #--------------------------------------------------------
Christian> # Server version 3.22.22
Christian> #
Christian> # Table structure for table 'bin'
Christian> #
Christian> CREATE TABLE bin (
Christian> extra_chars tinyblob,
Christian> id tinyint(4)
Christian> );
Christian> But also the wrong data in 'sql\bin.txt':
Christian> abcdefg 0
Christian> abc'defg 1
Christian> abc"defg 2
Christian> abc\\defg 3
Christian> abc\0defg 4
Christian> abc\
Christian> defg 5
Christian> abc\ defg 6
Christian> As you can see the <newline> is converted to '\'+<newline>
> instead of '\n'.
Christian> Also the <tab> is converted to '\'+<tab> instead of '\t'.
Thanks for the test!
The above isn't actually a problem for mysqlimport:
A test run:
shell> mysqldump --tab=/tmp test bin
shell> mysqlimport --delete test /tmp/bin.txt
test.bin: Records: 7 Deleted: 0 Skipped: 0 Warnings: 0
shell> mysqldump test bin
# MySQL dump 6.0
#
# Host: localhost Database: test
#--------------------------------------------------------
# Server version 3.22.23-debug
#
# Table structure for table 'bin'
#
CREATE TABLE bin (
extra_chars tinyblob,
id tinyint(4)
);
#
# Dumping data for table 'bin'
#
INSERT INTO bin VALUES ('abcdefg',0);
INSERT INTO bin VALUES ('abc\'defg',1);
INSERT INTO bin VALUES ('abc\"defg',2);
INSERT INTO bin VALUES ('abc\\defg',3);
INSERT INTO bin VALUES ('abc\0defg',4);
INSERT INTO bin VALUES ('abc\ndefg',5);
INSERT INTO bin VALUES ('abc defg',6);
Which is exactly as it should be.
I have now updated the MySQL manual with the following to explain
this:
In the resulting text file, only the following characters are
escaped by the `ESCAPED BY' character:
* The `ESCAPED BY' character
* The first character in `FIELDS TERMINATED BY'
* The first character in `LINES TERMINATED BY'
Additionally `ASCII 0' is converted to `ESCAPED BY' followed by 0
(`ASCII 48').
The reason for the above is that one MUST escape any `FIELDS
TERMINATED BY', `ESCAPED BY' or `LINES TERMINATED BY' characters
to be able to reliable be able to read the file back. `ASCII 0' is
escaped to make it easier to view with some pages. As the above
file doesn't have to conform to any SQL syntax nothing else needs
to be escaped.
As the resulting file doesn't have to conform to the SQL syntax
nothing else needs to be escaped.
--
Note that the characters are only escaped, not escaped + converted.
Regards,
Monty
