Reputation: 15136

Hyphens in column names in MySQL DB

May be this question has been answered before but I couldn't find it.

I am using a 2/3 yr old MySQL database which has hyphens in its column names. When I try to use these names from my Java code, the names are broken at the hyphen (e.g. air_port becomes air) and thus are not found. I tried replacing hyphens to underscores in my code hoping that the DB might treat them equally but that doesn't work.

How can I escape the hyphen or how can I access these columns ? Could this be an issue of the character set being used ?

Upvotes: 29

Answers (6)

Hexodus

Reputation: 12927

It's better to not use hyphens in your column names. I suffered a big problem with JOIN statements where hyphens caused big trouble - there even escaping names in back ticks didn't work.

Convert the column names to use underscores - this is the safest way to go.

As an alternative - in case where even backticks should cause problems (you know it happend to me) and you want to stick to hypens no matter what - just create a VIEW to reflect the same table with all the fields and query the view instead of the original table.

Upvotes: 5

Rodolfo Velasco

Reputation: 855

I had to create a db named pre-ca_db.

I solved the problem with

create database `pre-ca_db`;

Good luck!

Upvotes: 1

Gaston

Reputation: 142

Hyphens in database names aren't good also. But you can use them with the backtick trick `

`name-with-hyphen`

Upvotes: 3

Robby Slaughter

Reputation: 1590

Do you have hyphens (-) or underscores (_) in your column names?

Hyphens are a big problem because if you end up mapping a column name to a variable, most languages do not like to have hyphens inside variable names. Perhaps you are using one of the Java libraries that automatically generates variables or objects whose names are based on column names.

Depending on the nature of your problem, there are a couple of different approaches you can use:

Rename all of your columns using ALTER TABLE. Be conscious that this could affect referential integrity or other applications that depend on the database. If you don't know what that means, don't do it.
Create SQL views that simple restate the tables you need but with "better" column names. This is not very efficient, but it will allow you to get what you want.
Use the AS keyword when running your SELECT statements to rename columns within queries.

None of these are great solutions, but they should get you started. Good luck!

Upvotes: 8