ReneFroger ReneFroger - 4 months ago 12
MySQL Question

Why is ALTER TABLE used for changing AUTO_INCREMENT variable?

With MySQL, when you want change the password, or character set, you can do it as following:


But when you want to change the autoincrement variable of a table, you need a different syntax for that:


This makes me to wonder if it would be more consistent if it was as following:


Because some
operations applies on the table. But I assume there must be some reasons for this syntax choice. When looking in the documentation, I found the following:

ALTER TABLE changes the structure of a table. For example, you can add
or delete columns, create or destroy indexes, change the type of
existing columns, or rename columns or the table itself. You can also
change characteristics such as the storage engine used for the table
or the table comment.

But with
keyword you could also change the structure of the table, I assume? Please correct me if I'm mistaken.


SET syntax for variable assignment enables you to assign values to different types of variables that affect the operation of the server or clients:

  • System variables. See Section 6.1.4, “Server System Variables”. System variables also can be set at server startup, as described in Section 6.1.5, “Using System Variables”.
  • User-defined variables. See Section 10.4, “User-Defined Variables”.
  • Stored procedure and function parameters, and stored program local variables. See Section 14.6.4, “Variables in Stored Programs”.


The SET is an operation for the whole server / database. You can't set the AUTO_INCREMENT with SET only for a specific table. The AUTO_INCREMENT information is stored on the table itself. It's not a global variable because some tables can have a AUTO_INCREMENT=1 and others AUTO_INCREMENT=[another-int-value].

To start with an AUTO_INCREMENT value other than 1, set that value with CREATE TABLE or ALTER TABLE, like this: ALTER TABLE tbl AUTO_INCREMENT = 100;