23

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

can anyone provide any output on this?

n3rd
  • 5,989
  • 4
  • 39
  • 56
MySQL DBA
  • 5,692
  • 21
  • 54
  • 71

3 Answers3

40

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`;
Andre Miller
  • 15,255
  • 6
  • 55
  • 53
  • i want to create it dynamically, can i do that? for example - i want to give a database name to a variable set @a = name.surname; create database @a; – MySQL DBA May 29 '09 at 12:45
  • Thanks a lot Andre. It was a great help as i was needing to it, though it is not advisable. Thanks :) – MySQL DBA May 29 '09 at 13:17
  • How come some servers allow me to use \ in table names? – Petah May 04 '11 at 23:15
  • 1
    Why do you say these ` “/”, “\”, “.”` are not allowed? **create database backtick:backtick** works.. only the space at the end is an issue. In fact, mysql doesn't create a physical file `:` on disk, it changes the name appropriately. – nawfal Mar 21 '13 at 12:48
  • @nawfal, I was quoting that from the documentation. If you follow the 'restrictions' link, you will see it listed there. I am not sure why it is mentioned in the documentation if you can in fact create such tables. – Andre Miller Oct 20 '13 at 18:57
  • @AndreMiller, How do you escape the backticks? If we would like to have a backtick as the database name. – Pacerier Jan 19 '15 at 20:29
  • @Pacerier You can escape backticks in a MySQL identifier by doubling them. E.g., `myweird\`\`name`. If the name is dynamic, you also may want to strip out null characters, as they are disallowed, and software often doesn't handle them properly. – Chris Middleton May 22 '15 at 19:12
  • @ChrisMiddleton, Wow, Nice, we can actually do `create database````;`. Is there any way to do that for tables as well? – Pacerier May 24 '15 at 14:17
  • @Pacerier That method applies to all mysql identifiers - dbs, tables, columns, and indexes - though I wouldn't advise doing it. Ex: `ALTER TABLE \`test\`\`\`.\`test\`\`\` ADD UNIQUE \`test\`\`\` ( \`test\`\`\` ) ` (That's an index created on a column in a table in a database, where all of the former are named `test\``.) – Chris Middleton May 26 '15 at 17:06
  • This answer has been around a long time, but it's not correct. I referred to this answer before doing some work, and in my work I found that '-' hyphen characters are not allowed in database names. `CREATE DATABASE IF NOT EXISTS foo-bar;` gives `ERROR 1064 (42000): You have an error in your SQL syntax;` but then `CREATE DATABASE IF NOT EXISTS foo_bar;` works just fine. – Edward Ned Harvey Jul 26 '16 at 20:52
3

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;
soulmerge
  • 73,842
  • 19
  • 118
  • 155
1

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.
kriplozoik
  • 89
  • 1
  • 1