MySQL: dot (".") in database name

Question

Hello,

does MySQL allows to create database which has dot (".") in its name? I'm using MySQl 5.1.22.

Thanks

Answer 1

MySQL 5.0.22 doesn't appear to allow it:

% mysqladmin -uroot -pXXX create foo.bar
mysqladmin: CREATE DATABASE failed; error: 'Incorrect database name 'foo.bar''

Even it if it did allow it, I would strongly recommend against it.

At the very least you'd have to escape any reference to that database with backticks in every single query that ever uses it.

Answer 2

You can't use the dot in a database name. Also, I'd avoid using it in any identifier. A common convention is to use underscore instead. It will serve the same purpose and will avoid a LOT of confusion. If you do have a good reason for using strange and otherwise-illegal characters in a table or field name, then you have to escape it.

to escape identifiers in MySQL, use the backtick:

SELECT `select`, `some.field name`, `crazy()naming+here`
FROM `my-=+table`

Getting into the habit of backticking all field names regardless of whether you need to is a good practice in my opinion, but that's another story.

Answer 3

Before MySQL 5.1.6, database and table names cannot contain “/”, “\”, “.”, or characters that are not allowed in file names (see 8.2. Schema Object Names). In versions after 5.1.6 you have to quote your tablename with a backtick (“`”) - but as others also advised: you shouldn't do this to prevent any unnecessary trouble.

Answer 4

You can use "." in names from MySQL 5.1.6 according to the documentation.

However, as has been said and will said again, please don't do it. For every problem you think you're solving now you'll be creating five which will bite you later on. Since . is used to qualify names - e.g. database.table or table.column you'll have to quote your database name every time you use it.*

You can do this with backticks:

CREATE TABLE `do.not.do.this` (col INT);

or using double quotes if you set the following option:

SET sql_mode='ANSI_QUOTES';
CREATE TABLE "asking.for.problems" (col INT);

_{* Not strictly true - you have to quote any character that's not alphanumeric or _ or $ , but . is a particularly troublesome option to have in your names.}