lower_case_table_names support

There are 5 different modes for the MatrixOne case sensitivity, and the case parameter lower_case_table_names can be set to 0, 1, 2, 3, or 4.

Parameter Explanation

Setting Parameter Value to 0

Setting lower_case_table_names to 0 stores identifiers as the original strings, and name comparisons are case sensitive.

Examples

  1. set global lower_case_table_names = 0;
  2. create table Tt (Aa int);
  3. insert into Tt values (1), (2), (3);
  5. mysql> select Aa from Tt;
  6. +------+
  7. | Aa   |
  8. +------+
  9. |    1 |
  10. |    2 |
  11. |    3 |
  12. +------+
  13. 3 rows in set (0.03 sec)

Setting Parameter Value to 1

Setting lower_case_table_names to 1 stores identifiers as lowercase, and name comparisons are case insensitive.

Examples

  1. set global lower_case_table_names = 1;
  2. create table Tt (Aa int);
  3. insert into Tt values (1), (2), (3);
  5. mysql> select Aa from Tt;
  6. +------+
  7. | aa   |
  8. +------+
  9. |    1 |
  10. |    2 |
  11. |    3 |
  12. +------+
  13. 3 rows in set (0.03 sec)
  1. set global lower_case_table_names = 1;
  2. create table t(a int);
  3. insert into t values(1), (2), (3);
  5. -- Column aliases display the original string when the result set is returned, but name comparisons are case insensitive, as shown in the following example:
  6. mysql> select a as Aa from t;
  7. +------+
  8. | Aa   |
  9. +------+
  10. |    1 |
  11. |    2 |
  12. |    3 |
  13. +------+
  14. 3 rows in set (0.03 sec)

Setting Parameter Value to 2

Setting lower_case_table_names to 2 stores identifiers as the original strings, and name comparisons are case insensitive.

Examples

  1. set global lower_case_table_names = 2;
  2. create table Tt (Aa int);
  3. insert into tt values (1), (2), (3);
  5. mysql> select AA from tt;
  6. +------+
  7. | Aa   |
  8. +------+
  9. |    1 |
  10. |    2 |
  11. |    3 |
  12. +------+
  13. 3 rows in set (0.03 sec)

Setting Parameter Value to 3

Setting lower_case_table_names to 3 stores identifiers as uppercase, and name comparisons are case insensitive.

Examples

  1. set global lower_case_table_names = 3;
  2. create table Tt (Aa int);
  3. insert into Tt values (1), (2), (3);
  5. mysql> select Aa from Tt;
  6. +------+
  7. | AA   |
  8. +------+
  9. |    1 |
  10. |    2 |
  11. |    3 |
  12. +------+
  13. 3 rows in set (0.03 sec)

Setting Parameter Value to 4

Setting lower_case_table_names to 4 stores identifiers with `` as the original strings and case sensitive, while others are converted to lowercase.

Configuration Parameters

  • To configure globally, insert the following code in the cn.toml configuration file before starting MatrixOne:
  1. [cn.frontend]
  2. lowerCaseTableNames = "0" // default is 1
  3. # 0 stores identifiers as the original strings and name comparisons are case sensitive
  4. # 1 stores identifiers as lowercase and name comparisons are case insensitive
  5. # 2 stores identifiers as the original strings and name comparisons are case insensitive
  6. # 3 stores identifiers as uppercase and name comparisons are case insensitive
  7. # 4 stores identifiers with `` as the original strings and case sensitive, while others are converted to lowercase

When configuring globally, each cn needs to be configured if multiple cns are started. For configuration file parameter instructions, seeBoot Parameters for standalone installation.

Note

Currently, you can only set the parameter to 0 or 1. However, the parameter 2,3 or 4 is not supported.

  • To enable saving query results only for the current session:
  1. set global lower_case_table_names = 1;

When creating a database, MatrixOne automatically obtains the value of lower_case_table_names as the default value for initializing the database configuration.

Features that are different from MySQL

MatrixOne lower_case_table_names is set to 1 by default and only supports setting the value to 0 or 1.

The default value in MySQL:

  • On Linux: 0. Table and database names are stored on disk using the letter case specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case-sensitive.
  • On Windows: 1. It means that table names are stored in lowercase on disk, and name comparisons are not case-sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases.
  • On macOS: 2. Table and database names are stored on disk using the letter case specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case-sensitive.

Constraints

MatrixOne system variable lower_case_table_names does not currently support setting values 2, 3, or 4.