CODEWITHSUNDEEP
phpmysqlsqldatabase-design
Run
Run

Reputation: 57176

Convention for naming table in MySQL database

Can I use symbols for the table names in a database? For instance,

$tablename
%tablename

I tested with a select query and they seems OK but I haven't seen anyone done that before so just want to know if it is safe to do.

SELECT * FROM $tablename // returns result.
SELECT * FROM %tablename // returns result.

Upvotes: 1

Views: 392

Answers (3)

Hamad
Hamad

Reputation: 207

Quick answer: Yes!

You can use any symbols your database supports, but that does not mean its a good idea to do so!

I would suggest following standard naming conventions for the sake of readability and usability. There are many standard naming conventions listed online and widely accepted and used. Keep in mind your database does not work alone, it interfaces with applications and addons you might want to add later that might not support special characters.

Upvotes: 4

Michele Spagnuolo
Michele Spagnuolo

Reputation: 932

According to the MySQL 5.0 Reference Manual:

  • 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

  • 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.

  • Database and table names cannot contain “/”, “\”, “.”, or characters that are not permitted in file names.

$ is ASCII, so it is OK unquoted, % should be quoted with backticks when referencing it.

Upvotes: 1

Michael Berkowski
Michael Berkowski

Reputation: 270599

See documentation on valid identifiers.. You can use these, however you will need to quote any table or column name containing % (at least according to the documentation). $ is valid in unquoted identifiers, but % is not.

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

So, it is generally safe to do so, but you'll need to be consistent with quoting identifiers like

SELECT * FROM `%tablename`

Upvotes: 3

Related Questions