By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,098 Members | 1,950 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,098 IT Pros & Developers. It's quick & easy.

Export/import of column names with space

P: n/a
Hello,

I am using MySQL 4.0.
Say that I have a table named "mytable" having a column "ID" and a
column "test columnname".

When dumping:

mysqldump --opt DATABASE -uUSER -hHOST -pPASS > myfile.txt

it gives someting like:

CREATE TABLE mytable(ID int(11) NOT NULL, test columnname text NOT
NULL);

Then I use the mysql command to import myfile.txt in another database:

mysql DATABASE -uUSER -hHOST -pPASS < myfile.txt

but it fails with:

"ERROR 1064 at line 1: 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 'test columnname text NOT NULL)' at line 1"

Obviously, the mysql command can't handle column names with spaces. Is
this a bug? I would say so - mysqldump should put the column names
between quotes or so (alternatively, mysql shouldn't allow spaces in
column names).

My problem is that I have to work with these columns as they are.
They're also referred to from a lot of queries in the API's. Any
possible workarounds?

Thanks
Bart
Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Bart Van der Donck wrote:
I am using MySQL 4.0.
Say that I have a table named "mytable" having a column "ID" and a
column "test columnname".

When dumping:

mysqldump --opt DATABASE -uUSER -hHOST -pPASS > myfile.txt

it gives someting like:

CREATE TABLE mytable(ID int(11) NOT NULL, test columnname text NOT
NULL);

Then I use the mysql command to import myfile.txt in another database:

mysql DATABASE -uUSER -hHOST -pPASS < myfile.txt

but it fails with:

"ERROR 1064 at line 1: 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 'test columnname text NOT NULL)' at line 1"

Obviously, the mysql command can't handle column names with spaces. Is
this a bug? I would say so - mysqldump should put the column names
between quotes or so (alternatively, mysql shouldn't allow spaces in
column names).

My problem is that I have to work with these columns as they are.
They're also referred to from a lot of queries in the API's. Any
possible workarounds?


Dump the data with the "-Q" / "--quote-names" flag. This puts backticks
around all table and column names and prevents this problem from happening.

eg: mysqldump --opt DATABASE -Q -uUSER -hHOST -pPASS > myfile.txt

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/
Jul 20 '05 #2

P: n/a
Chris Hope wrote:
Dump the data with the "-Q" / "--quote-names" flag. This puts backticks
around all table and column names and prevents this problem from happening.

eg: mysqldump --opt DATABASE -Q -uUSER -hHOST -pPASS > myfile.txt


Thanks Chris, that's what I was looking for.
However, illogical mysql behaviour I think...
When one can create column names with spaces by default, mysqldump
should also use backticks by default. (Or the opposite, not allowing
spaces and then not using backticks in dump)

Anyway, it works fine now.

Bart
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.