DDL (Data Definition Language (DDL)); DDL (Data Definition Language (DDL)); DDL (Data Definition Language (DDL)); DDL (Data Definition Language (DDL));

CREATE TABLE `test` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `age` int(5) DEFAULT NULL,
  `age1` int DEFAULT NULL,
	`name` varchar(5) DEFAULT NULL,
	`name1` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

varchar(n)

Varchar (n) is the length of a string.

INSERT INTO `test`(`age`, `age1`, `name`, `name1`) VALUES (12345, 12345, 'ganhuojun', 'ganhuojun');
Copy the code

The running results are as follows:

1406 - Data too long for column 'name' at row 1, Time: 0.001000s
Copy the code

Varchar (5) allows only 5 characters in length.

INSERT INTO test(age, age1, name, name1) VALUES (12345, 12345, ‘ganhu’, ‘ganhuojun’); The result is as follows, indicating that the name field can be successfully stored if the length of the field is reduced to 5

Affected Rows: 1, Time: 0.001000s Verifies that the n in varchar(n) does refer to the string length

int(n)

In mysql database, int is four bytes, one byte is eight bits, i.e. 4 * 8 = 32, i.e. 2^32 digits.

Varchar (n) = 123456; varchar(n) = 123456;

INSERT INTO `test`(`age`, `age1`, `name`, `name1`) VALUES (123456, 123456, 'ganhu', 'ganhuojun');
Copy the code

Varchar (n) does not limit the length of data.

Affected rows: 1, Time: 0.001000s
Copy the code

So what does the n in int(n) mean?

The n in int(n) actually refers to the maximum display width. The maximum effective display width is 255, and the display width is independent of the storage size or the range of values contained by the type.

ZEROFILL = 0; ZEROFILL = 0; ZEROFILL = 0; ZEROFILL = 0; ZEROFILL = 0;

CREATE TABLE `test1` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `age` int(5) ZEROFILL DEFAULT NULL,
  `age1` int ZEROFILL  DEFAULT NULL,
	`name` varchar(5) DEFAULT NULL,
	`name1` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

At this point, we insert two groups of data to compare the results:

INSERT INTO `test1`(`age`, `age1`, `name`, `name1`) VALUES (1, 123456, 'ganhu', 'ganhuojun');
INSERT INTO `test1`(`age`, `age1`, `name`, `name1`) VALUES (123456, 123456, 'ganhu', 'ganhuojun');
Copy the code

After execution, the result is shown in the following figure. As 1 has only one width, four zeros are added in front of it to reach five bits, while 123456 is already six bits, which is normal.

It is verified here that when the width of the inserted data is smaller than the defined n, 0 will be used to complete the data. When the width exceeds the defined N, it can also be displayed normally. Note that int(n) does not limit the size of the actual int, and can still hold up to 4 bytes of data.