Description:
When I executed ALTER TABLE example_table MODIFY request_id VARCHAR(50) NOT NULL;, the column name case was changed. The column name unexpectedly changed from uppercase (REQUEST_ID) to lowercase (request_id).
How to repeat:
1、Use uppercase for defining database column names (REQUEST_ID).
CREATE TABLE example_table (
ID INT AUTO_INCREMENT PRIMARY KEY,
REQUEST_ID VARCHAR(20) NOT NULL
);
2、Use lowercase column names (request_id) in the 'ALTER TABLE ... MODIFY' statement.
ALTER TABLE example_table MODIFY request_id VARCHAR(50) NOT NULL;
3、Describe the table structure.
desc example_table;
+-----------------+---------------+------+-----+----------------------+--------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+----------------------+--------------------------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| request_id | varchar(50) | YES | | NULL | |
+-----------------+---------------+------+-----+----------------------+--------------------------------+
2 rows in set (0.00 sec)
The column length was modified, and at the same time, the column name changed from uppercase (REQUEST_ID) to lowercase (request_id).
Suggested fix:
In the MySQL development manual, at the address: https://dev.mysql.com/doc/refman/8.4/en/alter-table.html, in the section “15.1.9 ALTER TABLE Statement”, in the part “Renaming, Redefining, and Reordering Columns” it mentions:
MODIFY:
Can change a column definition but not its name.
More convenient than CHANGE to change a column definition without renaming it.
With FIRST or AFTER, can reorder columns.
The document then provides an example:
To change a column definition but not its name, use CHANGE or MODIFY. With CHANGE, the syntax requires two column names, so you must specify the same name twice to leave the name unchanged. For example, to change the definition of column b, do this:
ALTER TABLE t1 CHANGE b b INT NOT NULL;
MODIFY is more convenient to change the definition without changing the name because it requires the column name only once:
ALTER TABLE t1 MODIFY b INT NOT NULL;
I hope the ALTER TABLE ... MODIFY command does not change the column name. Regardless of whether the name following MODIFY is in uppercase or lowercase, the original column name should remain unchanged. As written in the development manual，MODIFY Can change a column definition but not its name.
Description: When I executed ALTER TABLE example_table MODIFY request_id VARCHAR(50) NOT NULL;, the column name case was changed. The column name unexpectedly changed from uppercase (REQUEST_ID) to lowercase (request_id). How to repeat: 1、Use uppercase for defining database column names (REQUEST_ID). CREATE TABLE example_table ( ID INT AUTO_INCREMENT PRIMARY KEY, REQUEST_ID VARCHAR(20) NOT NULL ); 2、Use lowercase column names (request_id) in the 'ALTER TABLE ... MODIFY' statement. ALTER TABLE example_table MODIFY request_id VARCHAR(50) NOT NULL; 3、Describe the table structure. desc example_table; +-----------------+---------------+------+-----+----------------------+--------------------------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+---------------+------+-----+----------------------+--------------------------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | request_id | varchar(50) | YES | | NULL | | +-----------------+---------------+------+-----+----------------------+--------------------------------+ 2 rows in set (0.00 sec) The column length was modified, and at the same time, the column name changed from uppercase (REQUEST_ID) to lowercase (request_id). Suggested fix: In the MySQL development manual, at the address: https://dev.mysql.com/doc/refman/8.4/en/alter-table.html, in the section “15.1.9 ALTER TABLE Statement”, in the part “Renaming, Redefining, and Reordering Columns” it mentions: MODIFY: Can change a column definition but not its name. More convenient than CHANGE to change a column definition without renaming it. With FIRST or AFTER, can reorder columns. The document then provides an example: To change a column definition but not its name, use CHANGE or MODIFY. With CHANGE, the syntax requires two column names, so you must specify the same name twice to leave the name unchanged. For example, to change the definition of column b, do this: ALTER TABLE t1 CHANGE b b INT NOT NULL; MODIFY is more convenient to change the definition without changing the name because it requires the column name only once: ALTER TABLE t1 MODIFY b INT NOT NULL; I hope the ALTER TABLE ... MODIFY command does not change the column name. Regardless of whether the name following MODIFY is in uppercase or lowercase, the original column name should remain unchanged. As written in the development manual，MODIFY Can change a column definition but not its name.