Warning using reserved words in MySQL

[DanielCoe]

Tweet

Hi,

After a fun packed morning of importing a database backup again, I have found that I had some reserved MySQL words as column names in my tables.

Now you would have thought that PHPAdmin would not allow these in - nor would MySQL, but having such previously ok column names such as 'FROM' or 'OPTION' is not a good idea, as if you use the command:

mysql -h**** -u***** -p***** db_name < my.dump

to re-install backups like me, MySql does not like these names as column names.

Thought I'd just share this knowledge.

Daniel

[Rich]

Yeah, always used to use desc for description columns and it caused all sorts of headaches. You need to surround them with ` (next to the 1 key) to get mysql to ignore them. My version of PhpMyAdmin does this for me so it sounds like your my.dump has been made with out these.

[DanielCoe]

Hi Mate,

Yeah, I normally do a straight:

mysqldump -h <host name> -u<username> -p<password> <db name> > my-dump.file

Is there a command I can add into the mysqldump command to add these ```'s?

Dan

[DanielCoe]

There is just so much I could learn about MySQL, I still haven't got around to learning everything about Oracle yet after 10yrs, and that's my job!

Found this:

--quote-names, -Q

Quote database, table, and column names within '`' characters. If the server SQL mode includes the ANSI_QUOTES option, names are quoted within '"' characters. As of MySQL 4.1.1, --quote-names is on by default, but can be disabled with --skip-quote-names.

And this:

--allow-keywords

Allow creation of column names that are keywords. This works by prefixing each column name with the table name.

Dan

[toneharb]

Loads of underscores in table names caused sql errors when importing . Learnt the hard way !

Also spaces in field names - works fine for inserts updates deletes even backing up but restoring db - big problems .