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?

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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 `<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="4c3e292d2020350c3f383e2d222b29">[email protected]</a>*database$name`;

Method 2

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;

Method 3

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 [email protected] 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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating