MariaDB SQL Grammar

Identifier Names: Understanding Rules and Conventions

In database systems, identifiers such as databases, tables, columns, indexes, aliases, and views are used to define objects that make up a database schema. Identifiers have specific rules and conventions for naming to ensure they are unique and unambiguous. In this article, we will discuss the rules and conventions for naming identifiers in MariaDB, a popular open-source relational database management system.

Unquoted Identifiers

Unquoted identifiers are alphanumeric sequences that consist of ASCII characters, extended Unicode characters, dollar sign, or underscore. These identifiers cannot begin with a numeral unless quoted, but they can contain numerals as long as they are not the first character. They are not case-sensitive unless quoted. For example, user_name and user_name are equivalent, but User_Name is different from the other two.

Quoted Identifiers

Quoted identifiers allow for more flexibility in naming objects in a database schema. They can include all printable characters except the null character (U+0000) and the ASCII double quote character ("). Quoted identifiers can also contain the quote character if it is quoted. When using quoted identifiers, they are case-sensitive, meaning that user_name and User_Name are two different identifiers.

Quote Characters MariaDB supports using two types of quote characters for quoted identifiers. The backtick character (`) is the default, and it can be used to enclose any identifier, even if it contains spaces or special characters. Alternatively, the ANSI_QUOTES SQL_MODE flag can be set to allow the use of double quotes (") for quoting identifiers. If the MSSQL flag is set, square brackets ([ and ]) can be used for quoting.

Further Rules The following rules also apply to identifier names in MariaDB:

• Identifier names are stored in Unicode (UTF-8).

• Database, table, and column names cannot end with space characters.

• Identifier names cannot contain the ASCII null character (U+0000) and supplementary characters (U+10000 and higher).

• User variables cannot be used as an identifier or as part of an identifier in SQL statements.

• Names such as 5e6 and 9e are not prohibited, but it is strongly recommended not to use them, as they could lead to ambiguity in certain contexts.

Maximum Length Identifiers in MariaDB have a maximum length of 64 characters for databases, tables, columns, indexes, constraints, stored routines, triggers, events, views, tablespaces, servers, and log file groups. Compound statement labels have a maximum length of 16 characters, while aliases have a maximum length of 256 characters, except for column aliases in CREATE VIEW statements, which are checked against the maximum column length of 64 characters (not the maximum alias length of 256 characters). Usernames have a maximum length of 80 characters, and roles have a maximum length of 128 characters. Multi-byte characters do not count extra towards the character limit.

Multiple Identifiers MariaDB allows the use of multiple identifiers to reference a specific object in a database schema. A period (.) is used to separate identifiers, and the period can be surrounded by spaces. For example, test.t1.i references column i in table t1 within the test database. This allows for the creation of fully qualified names for objects in a database schema, eliminating ambiguity when referencing them.

So Naming conventions and rules for identifiers in MariaDB ensure that object names are unique, unambiguous, and easy to use in SQL statements. Understanding these conventions and following best practices for naming database objects can make it easier to manage a database schema and improve the overall organization and clarity of the code.

Identifier Case-sensitivity

When working with MariaDB, it's important to understand the rules around identifier case-sensitivity. This is partly determined by the underlying operating system, with Unix-based systems being case-sensitive and Windows being case-insensitive by default (though Mac OS X can be configured either way).

In MariaDB, database and table names, aliases, and trigger names are affected by the system's case-sensitivity, while index, column, column aliases, stored routine, and event names are never case-sensitive. The lower_case_table_names server system variable plays a key role in determining whether table and database names are compared in a case-sensitive manner.

By default, on Unix-based systems, this variable is set to 0, meaning that table and database names are compared in a case-sensitive manner. On Windows, it is set to 1, meaning that names are stored in lowercase and not compared in a case-sensitive manner. On Mac OS X, it is set to 2, meaning that names are stored as declared but compared in lowercase.

While it is possible to make Unix-based systems behave like Windows and ignore case-sensitivity, the reverse is not true, as the underlying Windows filesystem cannot support this. Even on case-insensitive systems, it is important to use the same case consistently within the same statement.

It's important to note that lower_case_table_names is a database initialization parameter, meaning that it must be set before running mysql_install_db and will not change the behavior of servers unless applied before the creation of core system databases.

Overall, understanding the rules around identifier case-sensitivity in MariaDB is an important part of developing and maintaining databases in this system.

Binary Literals

Binary literals in MySQL can be represented in three formats - b'value', B'value' or 0bvalue. These literals are used to represent binary strings composed of 0 and 1 digits. They are useful when working with VARBINARY, BINARY, or BIT values.

To print the binary string value of a binary literal, use the SELECT statement followed by the binary literal.

For example,

SELECT 0b1000001 will print the binary string 'A'.

To convert a binary literal to an integer value, simply add 0 to it.

For instance,

SELECT 0b1000001+0 will return the integer value 65.

Binary literals are useful in situations where binary strings need to be represented explicitly in a query. They are particularly useful in conjunction with the BIT data type.

More Details:

https://github.com/AgeDB-Enterprise/maria-server

https://github.com/apache/age