Special Characters in MySQL Table Name

Question

I created a table as follows:

CREATE TABLE IF NOT EXISTS 'e!' (
`aa` int(11) unsigned NOT NULL auto_increment,
`showName` TEXT NOT NULL default '',
`startDateTime` DATETIME NOT NULL default '',
`endDateTime` DATETIME NOT NULL default '',
PRIMARY KEY  (`aa`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8

Then tried to insert with the query:

INSERT INTO e! (showname, startDateTime, endDateTime) VALUES('E! News ', '2012-05-03 19:00:00', '2012-05-03 20:00:00')

And it errors due to the ! in the table name, I'm assuming ! is a special character in mysql. I tried to escape it but the query still failed.

So, can I have special characters like ! or & in the table name? If yes, then I probably have to encode them somehow?

Thanks.

score 26 · Accepted Answer · answered May 04 '12 at 05:24

26

Quote your ambiguous or "special" table names with a back tick:

INSERT INTO `e!` ...

Or better, don't use special characters in table names to avoid such problems.

answered May 04 '12 at 05:24

deceze

510,633
85
743
889

score 13 · Answer 2 · answered May 04 '12 at 05:22

According to the docs, you can't:

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

Source: http://dev.mysql.com/doc/refman/5.5/en/identifiers.html

score 4 · Answer 3 · answered May 04 '12 at 05:25

Try with this:

    CREATE TABLE IF NOT EXISTS `e!` (
`aa` int(11) unsigned NOT NULL auto_increment,
`showName` TEXT NOT NULL default '',
`startDateTime` DATETIME NOT NULL ,
`endDateTime` DATETIME NOT NULL ,
PRIMARY KEY  (`aa`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8

score 2 · Answer 4 · answered May 04 '12 at 05:32

2

If you have whatever special requirements for the table identifiers, that means there is something wrong with your database architecture and/or with understanding database architecture.

You'd better correct these architectural mistakes instead of enforcing silly identifiers

answered May 04 '12 at 05:32

Your Common Sense

156,878
40
214
345

I definitely agree it's better not to use odd identifiers in the table names, I know I could easily map the specials to another valid string. – peasant13337 May 04 '12 at 06:00
LOL @peasant13337 you didn't get the point. there should be no *need* in them, no matter disguised or not. you just don't understand the purpose of these identifiers – Your Common Sense May 04 '12 at 06:24

score 0 · Answer 5 · edited Feb 18 '17 at 02:34

0

You need back-ticks around the e!

Also, your datetimes need default values that resolve to a datetime.

edited Feb 18 '17 at 02:34

River

8,585
14
54
67

answered May 04 '12 at 05:29

sdjuan

709
6
15

score 0 · Answer 6 · answered May 22 '19 at 11:42

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.

Source: https://dev.mysql.com/doc/refman/8.0/en/identifiers.html

score -1 · Answer 7 · answered May 04 '12 at 05:33

-1

This is Object Names standards for MySQL. According it you can't use "!" symbol as part of table name.

answered May 04 '12 at 05:33

Valeriy Gorbatikov

3,459
1
15
9

2

["Any character is legal in database or table identifiers except ASCII NUL (X'00')"](https://dev.mysql.com/doc/refman/5.5/en/identifier-mapping.html) – Nisse Engström Feb 18 '17 at 07:57

Special Characters in MySQL Table Name

7 Answers7

Linked