Introduction to the

This paper mainly explains the following two issues:

  • What happens if the primary key value range is out of bounds?
  • Do not set the primary key ok?

After reading the text, you can find the answer in the summary section.

I recommend two articles about MySQL:

  • www.veryitman.com/2019/12/07/…
  • www.veryitman.com/2019/11/10/…

The boundary value of a numeric type

C Standard library limits. H defines the value range and limit value of some data types. That is, the value of this type cannot be greater than the upper bound or less than the lower bound of the value of this type.

Let’s take a look at the following C example.

#include <stdio.h>
#include <limits.h>

int main(a) {
    / / 65535
    printf("unsigned short max value: %u\n", USHRT_MAX);
    unsigned short yy = USHRT_MAX;
    printf("xx = %hu\n", yy); / / 65535
    printf("xx + 1 = %hu\n", yy+1); / / 0
    printf("xx + 2 = %hu\n", yy+2); / / 1

    printf("unsigned short sizeof: %u\n".sizeof(unsigned short));

    return 0;
}
Copy the code

Compile and run, you can get the following results:

unsigned short max value: 65535
xx = 65535
xx + 1 = 0
xx + 2 = 1
unsigned short sizeof: 2
Copy the code

As a result, the maximum value of the unsigned short data type is 65535, and of course the minimum value is 0, which ranges from 0 to 65535.

Here, I would like to mention some instructions about formatting and printing:

  • Both I % and D % can be used to represent signed decimal integers, generally ld% is used to represent long;

  • O % is used to represent octal integers, x% (x%) is used to represent hexadecimal integers;

  • Short is prefixed with h, so %hd means short integers in decimal and %ho means short integers in octal.

  • %u represents an unsigned integer, that is, an unsigned int;

  • Both the h% and l% prefixes can be used in combination with u% to represent unsigned integers. For example, %lu indicates printing unsigned long, %hu indicates printing unsigned short.

  • Print the value of long, %ld print the value of long, %lx print the long integer in hexadecimal format, %lo print the long integer in octal format;

practice

After the above explanation, then we can enter today’s main topic.

Create database play_db

CREATE DATABASE play_db;

USE play_db;
Copy the code

Set the primary key and increment it

Create a table table0

CREATE TABLE table0(id SMALLINT unsigned AUTO_INCREMENT PRIMARY KEY, name char(10)) AUTO_INCREMENT=65535;
Copy the code

SMALLINT is a data type supported by MySQL and occupies two bytes. The value range of SMALLINT is -32 768,32 767, and the value range of unsigned is 0,65 535.

Take a look at the tables created

 DESC table0;
Copy the code

Insert data

INSERT INTO table0 (name) VALUES ('veryitman');
Copy the code

The default id is 65535.

INSERT INTO table0 (name) VALUES ('.com');
Copy the code

So the other question is, what if we don’t set the primary key?

Primary key no autoincrement

Create a table table1

TINYINT Unsigned. The value ranges from 0 to 255

CREATE TABLE table1(id TINYINT unsigned, name char(10));

INSERT INTO table1 (id, name) VALUES (255.'veryitman');

INSERT INTO table1 (id, name) VALUES (256.'haha');
Copy the code

ERROR 1264 (22003): Out of range value for column ‘id’ at row 1

If no primary key is set, InnoDB automatically creates an invisible 6 byte row_id for you. This row_id is the global dictsys.row_id maintained by InnoDB. The global ROW_ID is incremented by one each time a data entry is inserted. (Tables that do not define a primary key use the global ROW_ID as the primary key ID.)

If the global ROW_ID continues to grow until it reaches 2 ^ 48-1, then an additional increment will set the lower 48 bits of the row_ID to 0. If an additional row is inserted, the row_ID will be 0, and a primary key conflict is possible.

Therefore, when creating a table, it is best to set the primary key.

Set autoincrement without primary key

Create a table table2

CREATE TABLE table2(id TINYINT unsigned AUTO_INCREMENT, name char(10)) AUTO_INCREMENT=255;
Copy the code

The following error message is displayed:

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
Copy the code

The error message means that there can only be one increment column, which must be defined as a key.

summary

1, in the design of the data table, try to choose the appropriate field type according to the business requirements. After the increment ID of the database table reaches the upper limit, its value will not change when applying again, and the primary key conflict error will be reported when continuing to insert data.

Try not to have too much data in a library or table, and set it according to your business. If there is a large number of data to be divided into database and table, pay attention to the generation of primary keys in distributed environment to ensure that the primary key is unique.

2. In order to avoid unnecessary trouble and hidden errors, all design tables should have primary keys.

The appendix

Value types supported by MySQL

Highly recommended. – Don’t miss it. What if it helps you? Friends made a tutorial about artificial intelligence, tutorial is not only zero basis, easy to understand, but also very humorous, like reading a novel! Click here to jump to the tutorial, interested in artificial intelligence friends can go to understand.

Short step, no thousands of miles; Not small streams, beyond into rivers and seas.