1) Change the column type to Unsigned Int or BigInt
#MYSQL CREATE TABLE WITH AUTOINCREMENT UPDATE#
Or, we can create a copy of the table and update the auto-increment value of that table copy. We can either change the data type to Unsigned Int or Big Int which has higher maximum value. There can be two ways to fix the auto_increment ID exhaustion issue. SHOW TABLE STATUS FROM `your_database_name` WHERE `name` LIKE 'your_table_name' \G Get the auto-increment value of a single table SELECT `AUTO_INCREMENT`,`TABLE_NAME` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='your_database_name' ORDER BY AUTO_INCREMENT ASC Ref: Get the auto-increment value of database table Get the auto-increment value of all tables The values int(1), int(6), int(10), and int(11) will have the maximum value in a range that equals 2147483647 (for signed INT) and 4294967295 (for unsigned INT). The minimum and maximum values of the column and the storage size (in bytes) depend only on the integer type you use but not on the length. Note that the display width does not influence the maximum and minimum value ranges. Actually, display width makes difference when you set the ZEROFILL attribute. Thus, it matters only when displaying the output.Ĭonsider the following: If you specify 5 as a value for the int(1), int(6), int(10), and int(11) columns, they all will output the same value – 5. how many characters should be displayed in the column when retrieving data for the column. The display width is the number in parentheses that represents the output length, i.e. In MySQL, you can set the display width for the INT data type. Here’s a list of the different integer types in MySQL and their maximum value: Create foreign keys in the newly copied tableĭifferent integer types have different maximum values.Check the auto_increment value of the table.
2) Create a copy of the table and update the auto-increment value.1) Change the column type to Unsigned Int or BigInt.
Get the auto-increment value of a single table.Get the auto-increment value of all tables.Get the auto-increment value of database table.
0 kommentar(er)
