Dot (.) in MySQL Database Names: Permissible or Not?

In MySQL, naming conventions play a crucial role in database management. One common question that arises is whether dot (.) characters are permissible in database names. Let's delve into the answer.

MySQL 5.1.22 restricts the use of dots in database names. This is primarily due to the potential for confusion and ambiguity when parsing object names. Database names are treated as identifiers by the MySQL server, and dots are often used to separate parts of identifiers.

To maintain clarity and avoid potential errors, best practices recommend avoiding dots in database names. Instead, underscores (_) serve as a suitable alternative for separating components, effectively achieving the same purpose without compromising readability.

However, in rare cases, there may be a compelling reason to use special characters in table or field names. In such scenarios, MySQL provides an escape mechanism using backticks (`). When an identifier contains characters that are otherwise illegal or would be interpreted differently by the server, enclosing it in backticks ensures that MySQL parses it as a literal string.

For instance, the following code demonstrates how to escape identifiers with backticks:

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

While this escape method may be necessary in specific cases, it's recommended to adhere to conventional naming practices and avoid special characters in database names where possible. Doing so promotes code readability, reduces potential errors, and ensures compatibility across different database systems.