What rules apply to naming a mysql column?

Question

In a MySQL table, naming a column, can I use

• spaces
• uppercase letters
• UTF8 characters

What other rules shall I follow?

( MySQL5 )

Solution 1

Yes, Yes, and Yes.

I like underscores between field names and no uppercase, but I don't want to start a flame war.

Another good reason to not use special characters in column names is you, or others are eventually going to have to type that over and over in your application. I'd stick to the standard english alphabet.

Good column names:

account_id
user_id
first_name

Bad column name:

Ȩ̬̩̾͛ͪ̈́̀́͘ ̶̧̨̱̹̭̯ͧ̾ͬC̷̙̲̝͖ͭ̏ͥͮ͟Oͮ͏̮̪̝͍M̲̖͊̒ͪͩͬ̚̚͜Ȇ̴̟̟͙̞ͩ͌͝
uraniumType☢☢☢☢

Solution 2

The authoritative source is http://dev.mysql.com/doc/refman/5.0/en/identifiers.html

I know for a fact...

• You can use uppercase letters
• You can have spaces but:

  "Database, table, and column names cannot end with space characters."

Identifiers are stored using Unicode (UTF-8). This applies to identifiers in table definitions that are stored in .frm files and to identifiers stored in the grant tables in the mysql database. The sizes of the identifier string columns in the grant tables are measured in characters. You can use multibyte characters without reducing the number of characters permitted for values stored in these columns, something not true prior to MySQL 4.1. As indicated earlier, the permissible Unicode characters are those in the Basic Multilingual Plane (BMP). Supplementary characters are not permitted.

Solution 3

From mysql 8.0 reference manual at https://dev.mysql.com/doc/refman/8.0/en/identifiers.html:

"Identifiers are converted to Unicode internally. They may contain these characters:

Permitted characters in unquoted identifiers:

ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore)

Extended: U+0080 .. U+FFFF

Permitted characters in quoted identifiers include the full Unicode Basic Multilingual Plane (BMP), except U+0000:

ASCII: U+0001 .. U+007F

Extended: U+0080 .. U+FFFF

ASCII NUL (U+0000) and supplementary characters (U+10000 and higher) are not permitted in quoted or unquoted identifiers.

Identifiers may begin with a digit but unless quoted may not consist solely of digits.

Database, table, and column names cannot end with space characters."

Rules culled from this list of allowable characters: (1) ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore) (2) Full Unicode Basic Multilingual Plane (BMP) except U+0000 (3) Identifiers may begin with a digit but unless quoted may not consist solely of digits. (4) Database, table, and column names cannot end with space characters.

Interpretation: Re: ASCII: See ASCII character chart from widipedia article on ASCII (https://en.wikipedia.org/wiki/ASCII#/media/File:USASCII_code_chart.png) shows that between 0000 (null character) and 007F (delete character) is the entire ASCII chart, meaning all 15 cols x 7 rows are legal to use in column names, which clearly contradicts the rules that only punctuation allowed is "$" and "_". For example, "#" is located at _4 (x axis) 2 (y axis) and has identifiers "0023" / "35". But "#" it appears to be excluded by the ascii rules anyway (it's punctuation that's not dollar or underscore), so it's not clear whether ASCII columns can include the "#". (BTW many ASCII charts are labeled differently and you cannot find correlation with any " _ _ _" numerical index so look for variations in table indexing if you can't find anything close to '0000' format.)

Re: UNICODE BMP: is indexed by U+_ _ _ _ so rules are easier to implement. For example, "#" appears in the full BMP as U+0023 so it should be OK to use (U+000 is the "null" character, BTW).

So it also appears that special characters allowed for column names varies based on collation in your database; UTF collations offer more options for special characters to be used in column names, another reason to prefer utf collation in your database, in addition to its ability to include more international characters, something more valuable when constructing a website with international membership.

Comments

[brian]

I removed my down vote because you modified your stance on spaces :)