MySQL data types int, bigint, smallint, and tinyint and ID type transformations

bigint
Integer data (all numbers) from -2^63 (-9223372036854775808) to 2^63-1 (9223372036854775807). The storage size is 8 bytes.

Int
Integer data (all numbers) from -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647). The storage size is 4 bytes. The SQL-92 synonymous word for int is integer.

smallint
Integer data from -2^15 (-32,768) to 2^15-1 (32,767). The storage size is 2 bytes.

tinyint
Integer data from 0 to 255. The storage size is 1 bytes.

Sometimes when MySQL creates tables, the storage design is too small, and you can see the changes in the following ways:

1. When creating a table, define:
CREATE TABLE test (ID int primary KEY auto_increment)

2. Specify the starting value of the auto_increment when creating the table
CREATE TABLE test (ID int primary key auto_incrment) auto_increment = 100;
Start with 100 .... Default is 1;

3. When the self-increment property is removed, its default value becomes 0
ALTER TABLE test modify column ID int;

4. Add a Auto_increment property to a field
ALTER TABLE test Modify column ID int auto_increment;

5. Modify the initial value of a field
Alert table Test auto_increment = 200;

6. How to view the next self-increment ID value for a table's auto_increment
We know that the Getlastinsertid () property just gets the maximum ID after inserting the record, not what we want, so we use
Show table status like ' TableName ', containing auto_increment fields

Second:

Use INFORMATION_SCHEMA;
Select Auto_increment from tables WHERE table_name = ' table_name ';
Where Information_schema is not accessible to general users.

7. Modify global auto-increment parameters:
Use show variables like ' auto_increment% ';
We will see
Auto_increment_increment, Auto_increment_offset, which represent the global starting value and stepping, are modified as follows:
Set auto_increment_increment = 100;
Set auto_increment_offset = 10;
This will have an impact on the global auto_increment column, and it is recommended to use caution!

MySQL data types int, bigint, smallint, and tinyint and ID type transformations