#
# Test Mixed Case Identifiers with --lower-case-table-name=1
#
#
# Show how Mixed Case identifiers are handled for each OS type
#
SHOW VARIABLES LIKE 'lower_case_table_names';
Variable_name	Value
lower_case_table_names	1
CREATE SCHEMA MixedCaseSchema;
CREATE TABLE MixedCaseSchema.MixedCaseTable (MixedCaseColumn INT);
INSERT INTO MixedCaseSchema.MixedCaseTable (MixedCaseColumn) VALUES (1), (2);
SELECT * FROM MixedCaseSchema.MixedCaseTable;
MixedCaseColumn
1
2
USE MixedCaseSchema;
SHOW TABLES;
Tables_in_mixedcaseschema
mixedcasetable
USE test;
SHOW CREATE TABLE MixedCaseSchema.MixedCaseTable;
Table	Create Table
MixedCaseTable	CREATE TABLE `mixedcasetable` (
  `MixedCaseColumn` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT TABLE_NAME, TABLE_SCHEMA, TABLE_TYPE, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%ixed%';
TABLE_NAME	TABLE_SCHEMA	TABLE_TYPE	ENGINE
mixedcasetable	mixedcaseschema	BASE TABLE	InnoDB
SELECT FILE_NAME, FILE_TYPE, TABLESPACE_NAME, TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME LIKE '%ixed%';
FILE_NAME	FILE_TYPE	TABLESPACE_NAME	TABLE_SCHEMA	TABLE_NAME
./mixedcaseschema/mixedcasetable.ibd	TABLESPACE	mixedcaseschema/mixedcasetable	NULL	NULL
SELECT NAME, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE '%ixed%';
NAME	SPACE_TYPE
mixedcaseschema/mixedcasetable	Single
SELECT NAME, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE '%ixed%';
NAME	SPACE_TYPE
mixedcaseschema/mixedcasetable	Single
# restart
INSERT INTO MixedCaseSchema.MixedCaseTable (MixedCaseColumn) VALUES (3), (4);
SELECT * FROM MixedCaseSchema.MixedCaseTable;
MixedCaseColumn
1
2
3
4
USE MixedCaseSchema;
SHOW TABLES;
Tables_in_mixedcaseschema
mixedcasetable
USE test;
SHOW CREATE TABLE MixedCaseSchema.MixedCaseTable;
Table	Create Table
MixedCaseTable	CREATE TABLE `mixedcasetable` (
  `MixedCaseColumn` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT TABLE_NAME, TABLE_SCHEMA, TABLE_TYPE, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%ixed%';
TABLE_NAME	TABLE_SCHEMA	TABLE_TYPE	ENGINE
mixedcasetable	mixedcaseschema	BASE TABLE	InnoDB
SELECT FILE_NAME, FILE_TYPE, TABLESPACE_NAME, TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME LIKE '%ixed%';
FILE_NAME	FILE_TYPE	TABLESPACE_NAME	TABLE_SCHEMA	TABLE_NAME
./mixedcaseschema/mixedcasetable.ibd	TABLESPACE	mixedcaseschema/mixedcasetable	NULL	NULL
SELECT NAME, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE '%ixed%';
NAME	SPACE_TYPE
mixedcaseschema/mixedcasetable	Single
SELECT NAME, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE '%ixed%';
NAME	SPACE_TYPE
mixedcaseschema/mixedcasetable	Single
DROP TABLE MixedCaseSchema.MixedCaseTable;
DROP SCHEMA MixedCaseSchema;