CODEWITHSUNDEEP
mysql
CyberJunkie
CyberJunkie

Reputation: 22674

Do underscores in a MySQL table names cause issues?

Do underscores in table names affect performance or cause issues on some platforms?

For example, user_profiles

Would it be better to use userProfiles or is it just a matter of personal preference?

Upvotes: 37

Views: 57073

Answers (11)

alireza ranjbaran
alireza ranjbaran

Reputation: 1125

You can simply add grave accent (`) before and after column name. For example:

CREATE TABLE USERS(
   `PERSON_ID` NVARCHAR(100),
   `FNAME` NVARCHAR(255), 
   `LNAME` NVARCHAR(255),
   PRIMARY KEY (`PERSON_ID`)
);

Upvotes: 0

Unity
Unity

Reputation: 31

Nope, underscores in a database never cause any issues at all. My experience say that it is a better idea to identify any words in a database column.

If we use 'thisIsMyColumn' as a column name it's easy to write them, but 'this_is_my_column' as column name is more readable than the previous one.

Upvotes: 2

Dominique
Dominique

Reputation: 4332

You should avoid it. Although it is a permitted character in MySQL documentation, it appears it might cause trouble. For example, in MySQL 5.0 & 5.1 (and may be later versions), the query cache is never hit for queries involving a table name containing an underscore.

Upvotes: 2

Omar Al-Ithawi
Omar Al-Ithawi

Reputation: 5160

No, it's perfectly good. In fact, it is the most recommended naming from MySQL (based on who they name their internal tables!).

Be aware that naming that in Microsoft Windows the default MySQL behaviour is to lower-case your table names. This may cause problems. I am not sure what causes this.

However I personally prefer to name my tables like UserLikesPage, User and PostComment for example, since it reflects the class name in my code and I don't use Windows with MySQL.

Upvotes: 2

mike
mike

Reputation: 81

The only issue I've seen is that when using mysqlshow to view the structure of a table it appears to treat the underscore as a wildcard and returns only matching table names if there is an underscore in the name.

I could find no way to view the table structure of a table if there is an underscore in the name. I just discovered and confirmed this myself this morning.

I know this to be true of MySQL versions 4.0.18 and 4.1.22 for older versions and 5.1.52 for newer. Perhaps this is documented somewhere (I haven't taken the time to look yet), but it might be a perplexing thing for others, so I decided to mention it when I ran across this question when looking for information on the problem myself.

Upvotes: 8

Tarek
Tarek

Reputation: 3798

I never had issues with underscore when naming my tables. It's just personal preference.

Upvotes: 0

user756245
user756245

Reputation:

There is no problem using underscores. I think it is just personal preference.

Upvotes: 1

TikiTavi
TikiTavi

Reputation: 252

Many database visualization tools such as SQuirreL SQL and DbVisualizer also treat the underscore as a wildcard of sorts, grouping tables "matching" into a tree. For example, a table "document_a" and related tables "document_a_details", "document_a_history". In DbVisualizer, looking at the "document_a" table shows columns for all three tables.

This is generally not a problem, but can be confusing. For example, using SQuirreL SQL's graphing tools to generate ERDs combines columns from multiple tables into a single table and draws connectors for the relationships of all of these columns in the ERD. This results in relationships being drawn that don't actually exist.

For this reason, I would not include underscores in table names.

Upvotes: 0

Ben
Ben

Reputation: 1392

Nope. Underscores are perfectly legal in table names.

This page here in the MySQL documentation tells you about what characters are allowed.

Basically:

Permitted characters in unquoted identifiers:

ASCII: [0-9,a-z,A-Z$_]
Extended: U+0080 .. U+FFFF

Permitted characters in quoted identifiers:

ASCII: U+0001 .. U+007F
Extended: U+0080 .. U+FFFF

Personally I tend to stick with lowercase a-z, the occasional number, and underscores. But as @Vince said, it's just personal preference.

Upvotes: 50

psx
psx

Reputation: 4048

There's nothing wrong with using underscores, but keep in mind there may be occassions you need to escape the underscore, e.g. My\_Table

Upvotes: 4

Ross
Ross

Reputation: 46997

I found a few links to MySQL bugs that have either been marked closed or can't reproduce regarding underscores. As far as I know there are no issues - I always use underscores over camel-case and haven't experienced any problems.

Upvotes: 3

Related Questions