Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysql Create Database with special characters in the name

I want to create a database which name will have special characters in it. for example, (., - , _, @, #, $, %, &, *)

can anyone provide any output on this?

like image 742
MySQL DBA Avatar asked May 29 '09 12:05

MySQL DBA


3 Answers

I would strongly recommend that you do not create databases with such names. But if you absolutely must, here are the restrictions:

  • No identifier can contain ASCII NUL (0x00) or a byte with a value of 255.
  • Database, table, and column names should not end with space characters.
  • Database and table names cannot contain “/”, “\”, “.”, or characters that are not allowed in file names.

To create a database, you can do the following:

mysql> create database `really@strange*database$name`;
like image 72
Andre Miller Avatar answered Oct 03 '22 00:10

Andre Miller


Simple: Don't.

You can escape exotic table names using the backtick in mysql, but I don't know if you can use anything inside the backticks. It will give great amounts of pain during the rest of your software life cycle.

I would rather recommend creating another table to hold that exotic names.

-- Example:
CREATE TABLE _DatabaseMetadata (
    databaseName VARCHAR(255),
    exoticName VARCHAR(255)
) DEFAULT CHARSET=utf8;
like image 27
soulmerge Avatar answered Oct 02 '22 23:10

soulmerge


Short answer:

  • Don't. I strongly recommend to keep all identifiers consisting of A-Z,a-z,0-9 and _ characters. You can store Your "exotic" name in a column or comment.

Long answer:

  • You can name your columns, tables, keys, foreign keys, views, even databases using exotic characters but chances are You're gonna regret it in the future.
  • If You insist in doing that, You gonna need quoting Your identifiers in backticks (`).
  • In case Your identifier has to contain another ` inside, You can escape it stating it twice (e.g. exotic`name --> `exotic``name`)
  • For the things not to be so simple, if You use exotic (or even non-conventional) characters in the name of Your database (including a simple space), those characters (to my knowledge, everything except a-z,A-Z,0-9 and _) get escaped into 4-digit hexadecimal quadruplets escaped by @, e.g. `my database` becomes my@0020database. This form is used as a name of a directories/files in which Your databases/tables are stored, and e.g. items in information_schema.INNODB_SYS_FOREIGN, moreover may very well be OS-dependent (meaning, theoretically, You might want to run SHOW VARIABLES LIKE 'version_compile_os' to adapt to it). You see - with exotic names it all gets much, much more complicated and in the end it's not really worth it.
like image 42
kriplozoik Avatar answered Oct 03 '22 00:10

kriplozoik