Shulou(Shulou.com)05/31 Report--

This article mainly introduces what are the case rules of SQL sentences in MySQL. It is very detailed and has a certain reference value. Friends who are interested must finish it!

The case rules of SQL statements are related to the constituent elements of the statement, the referenced content, and the operating system used by the server.

1. SQL keyword and function name

The keywords and function names of SQL are not case-sensitive. For example, the following statements are equivalent:

SELECT NOW ()

Select now ()

SElEcT nOw ()

2. Database name, table name, and view name

MySQL represents databases and tables in terms of directories and files contained in the underlying file system of the server host. Therefore, the default case of database and table names depends on the naming rules of the operating system of the server host.

For example, the file name of the Windows system is not case-sensitive, so the MySQL server running on the Windows system does not have to distinguish between database names and table names. File names on Linux systems are case-sensitive, so MySQL servers running on Linux systems need to be case-sensitive between database names and table names. For the Mac OS X platform, the names in its file system are an exception, and they are not case-sensitive.

MySQL uses a file to represent a view, so the table-related above also applies to the view.

3. The name of the stored program

The names of stored functions, stored procedures, and events are not case sensitive. The name of the trigger is case-sensitive, which is different from the behavior of the standard SQL.

4. Column name and index name

In MySQL, neither column names nor index names are case sensitive. For example, the following statements are equivalent:

SELECT name FROM student

SELECT NAME FROM student

SELECT nAmE FROM student

5. The name of the alias

By default, aliases for tables are case-sensitive. Any case (uppercase, lowercase, or mixed case) can be used in the SQL statement to specify an alias. If you need to use the same alias multiple times in the same statement, you must keep their case consistent.

The following table summarizes whether SQL elements are case-sensitive on Windows and Linux systems.

When creating databases and tables under a Linux server, you should carefully consider case issues, such as whether they will be migrated to the Windows server in the future.

Suppose you create two tables, abc and ABC, on the Linux server, and there will be problems when you migrate them to the Windows server; because the Windows system is not case-sensitive, there is no difference between abc and ABC. You will also encounter problems when migrating tables from a Linux server to a Windows server.

To avoid case problems, you can first select a case scheme, and then follow that scheme all the time to create databases and tables.

It is mentioned in the MySql table-building specification of Alibaba's Java development manual:

[mandatory] Table names and field names must use lowercase letters or numbers, the beginning of numbers is prohibited, and only numbers are prohibited between the two underscores. The modification of the database field name is expensive because it cannot be pre-published, so the field name needs to be carefully considered.

In popular terms, MySQL is case-insensitive in Windows, but case-sensitive by default in Linux. Therefore, database names, table names, and field names are not allowed to appear in any uppercase letters to avoid complications.

It is generally recommended to use lowercase letters, and the InnoDB engine stores database and table names in lowercase letters internally. This can effectively prevent MySQL from causing case problems.

The above is all the content of the article "what are the case rules of SQL statements in MySQL?" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.