Skip to content

lower_case_table_names case sensitive support

lower_case_table_names is a global variable that MatrixOne sets whether the library table name is case sensitive.

Note

Unlike mysql, MatrixOne only supports0 and1 mode, and the default value is 1 for both Linux and mac systems.

View lower_case_table_names

Use the following command in MatrixOne to view lower_case_table_names:

show variables like "lower_case_table_names";--Default is 1

Set lower_case_table_names

Set lower_case_table_names in MatrixOne using the following command:

set global lower_case_table_names = 0;--Default is 1, and the reconnection to the database takes effect

Parameter explanation

parameter set to 0

Set lower_case_table_names to 0. The identifier is stored as a raw string, and the name is case sensitive.

Example

mysql> show variables like "lower_case_table_names";--View the default parameters, the default value is 1
+---------------------------+
| Variable_name | Value |
+---------------------------+
| lower_case_table_names | 1 |
+---------------------------+
1 row in set (0.00 sec)

set global lower_case_table_names = 0;--Reconnect to the database takes effect

mysql> show variables like "lower_case_table_names";--Reconnect the database to view the parameters, the modification is successful
+---------------------------+
| Variable_name | Value |
+---------------------------+
| lower_case_table_names | 0 |
+---------------------------+
1 row in set (0.00 sec)

mysql> use DB1;--Library name case sensitive
ERROR 1049 (HY000): invalid database DB1

mysql> use db1;
Database changed

create table Tt (Aa int);
mysql> insert into tt values ​​(1,2), (2,3), (3,4);--Table name case sensitive
ERROR 1146 (HY000): no such table db1.tt

insert into Tt values ​​(1), (2), (3);

mysql> select Aa from Tt;--name is case sensitive
+------+
| Aa |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.03 sec)

parameter set to 1

Set lower_case_table_names to 1. Identifiers are stored in lowercase, and names are case-insensitive.

Example

set global lower_case_table_names = 1;--Reconnect to the database takes effect

mysql> show variables like "lower_case_table_names";--Reconnect the database to view the parameters, the modification is successful
+---------------------------+
| Variable_name | Value |
+---------------------------+
| lower_case_table_names | 1 |
+---------------------------+
1 row in set (0.00 sec)

mysql> use DB1;---The library name is case-insensitive
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

create table Tt (Aa int,Bb int);
insert into tt values ​​(1,2), (2,3), (3,4);--Table name case insensitive

mysql> select Aa from Tt;--name is relatively case-insensitive
+------+
| aa |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.03 sec)

-- The alias of the column will display the original string when returning the result set, but the name comparison is case-insensitive. The example is as follows:
mysql> select Aa as AA,Bb from Tt;
+------+------+
| AA | bb |
+------+------+
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
+------+------+
3 rows in set (0.00 sec)