MySQL in our eyes int (10)

Self-summary, welcome to shoot Bricks!

Purpose: Is there a real difference between defining int (3) and int (10)?

Define a Student,student2 table separately

CREATE TABLE student (ID int (5) NOT NULL primary key,name varchar (20));

CREATE TABLE Student2 (ID int (TEN) NOT NULL primary key,name varchar (20));

Insert a single piece of data in each of these tables:

INSERT into student set id =007,name = ' tom ', insert into Student2 set id =007,name = ' tom ';

Check the records for two tables:

SELECT * from Student,student2;

Found no difference.

Then add an attribute value to the Student2 table ID field [Zerofill]:

ALTER TABLE STUDENT2 drop primary key;alter table Student2 Modify ID int (TEN) Zerofill primary key;

(When you modify a primary key field property, you need to first disassociate the primary key.) Otherwise the "Duplicate primary key" error is reported)

At this point, look at two more tables and you will find:

The length of the ID in the Student2 table is now known as the function of the [Zerofill] property: The value of the ID is 0 full to the length of the ID .

You can modify the length of the ID to try, for example, to int (3), int (5), int (10), which actually does not affect the actual length of the ID.

For example, write a record to the student table:

INSERT INTO student Set id = 555555,name = ' test007 ';

We will find:

Even if the length of the student table ID is 5, the display of records with an ID length greater than 5 will not be affected.

Summary: When you define a database int field, you generally do not need to set its length, but you can add the attribute [Zerofill] to differentiate it if you want to set it.

PS:1) Regardless of whether it is set to int (?), the actual length of int is 4 bytes (32 bits, the highest bit is distinguished by positive and negative numbers), so the maximum value is: 2^31, about 2 billion (10 bits 10 decimal).

2) Clear Table command: TRUNCATE TABLE student;

3) command line when executing multiple SQL commands, you can use the semicolon ";" in the English state. To split.

MySQL in our eyes int (10)