What happens to a fixed-size auto-criminal field after all available indices are used?



  • There's a field. INT with size Three. of the symbol autoincrement♪ The table contains dozens of records. These records are frequently updated and the old indices are removed rather than rewritten. What happens when the meaning of this field exceeds 999?



  • Number in parentheses close to the number of data types mysql - a popular misconception that doesn't read http://dev.mysql.com/doc/refman/5.7/en/numeric-type-attributes.html ♪

    The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column width display from being displayed correctly. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits.

    The number in parentheses has no influence. Customer applications may be drawn to the attention of clients, for example, to compare the tables, but there is no influence on the number. int(1) ID int(11)

    There will be nothing. It'll be 1,000, then 1001 and so on.

    What happens when the numerical limit is reached for the field at all - auto_increment will give the maximum value (but not a mistake directly!) and then the request will, in most cases, go down to the limit of the primary key, and only then return the error.

    mysql> create table testai (id smallint primary key auto_increment, val int not null);
    Query OK, 0 rows affected (0,01 sec)
    

    mysql> alter table testai auto_increment = 32765;
    Query OK, 0 rows affected (0,01 sec)
    Records: 0 Duplicates: 0 Warnings: 0

    mysql> insert into testai (val) values(1);
    Query OK, 1 row affected (0,01 sec)

    mysql> insert into testai (val) values(1);
    Query OK, 1 row affected (0,01 sec)

    mysql> insert into testai (val) values(1);
    ERROR 1062 (23000): Duplicate entry '32767' for key 'PRIMARY'
    mysql> select * from testai;
    +-------+-----+
    | id | val |
    +-------+-----+
    | 32765 | 1 |
    | 32767 | 1 |
    +-------+-----+
    2 rows in set (0,00 sec)

    mysql> insert into testai (val) values(5) on duplicate key update val=5;
    Query OK, 2 rows affected (0,00 sec)

    mysql> select * from testai;
    +-------+-----+
    | id | val |
    +-------+-----+
    | 32765 | 1 |
    | 32767 | 5 |
    +-------+-----+
    2 rows in set (0,00 sec)

    mysql>

    Because auto_increment continues to return a specific and allowable number, an example is interesting. on duplicate key update - he's gonna update the last line infinitely.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2