Notes for changing tables in mysql when auto-tuning is enabled

Mysql may modify tables in multiple cases. If you want to add an auto_increment for a table to implement automatic entire table, you need to pay attention to some problems. Otherwise, some errors may occur.

For example, to create a table:

Create table people (peopleid smallint not null, name CHAR (50) not null );

To modify a table in mysql, add an auto_increment for the peopleid so that the table can be automatically scaled up. mysql always reports the 1075 error at the beginning, so I checked the mysql 1075 error,

Error: 1075 SQLSTATE: 42000 (ER_WRONG_AUTO_KEY)

Message: an incorrect table definition can contain only one auto column and must be defined as a primary key.

You need to add a primary key first.

Alter table people add primary key (peopleid );

Alter table people change peopleid smallint auto_increment;

In this way, the mysql table is successfully modified.

If no primary key is added, is it okay?

If no primary key exists, you can:

Alter table people change peopleid smallint auto_increment unique;

This is also possible. Here we should note that if the field type is not indicated in every modification at the beginning, an error will always be reported, so we should not declare the field type.

The above are the issues that need to be paid attention to when mysql modifies the table to achieve automatic integer length.

Statement for modifying fields in mysql

Exception error due to insufficient MySQL database resources

How to delete multiple tables in MySQL

How MySQL connects to remote SQL

MySQL cluster introduction and Configuration