The length of int and varchar in MySQL is detailed

Int

Integer data (all numbers) from -2^31 (-2,147,483,648) to 2^31–1 (2,147,483,647). The storage size is 4 bytes. The SQL-92 synonym for int is integer

varchar

Length is 0-255 characters OH

What is the meaning of the numbers following the int in the MySQL field? What is the significance of the figures after varchar?

The code is as follows	Copy Code
Mysql> CREATE TABLE T (a int (1)); Query OK, 0 rows affected (0.10 sec) Mysql> INSERT into T values (123); Query OK, 1 row affected (0.02 sec) Mysql> INSERT into T values (12345678); Query OK, 1 row affected (0.03 sec) Mysql> select * from T; +----------+ | A | +----------+ | 123 | | 12345678 | +----------+

Visible, int (1) does not represent a byte.
If the longer number will not be the error?

The code is as follows	Copy Code
Mysql> INSERT into T values (1234567812345678); ERROR 1264 (22003): Out of Range value for column ' A ' in row 1 Mysql> INSERT into T values (2147483648); ERROR 1264 (22003): Out of Range value for column ' A ' in row 1 Mysql> INSERT into T values (2147483647); Query OK, 1 row affected (0.03 sec)

The maximum int-type length is 2^31-1, plus a signed number, which should be four bytes in length.

The code is as follows

Copy Code

Mysql> ALTER TABLE t add column B int; Query OK, 5 rows affected (0.25 sec) Mysql> insert INTO T values (2147483647,2147483648); ERROR 1264 (22003): Out of Range value for column ' B ' in row 1 mysql> insert into T values (2147483647,2147483647); br> Query OK, 1 row affected (0.03 sec) Mysql> select * from T; +------------+------------+ | A | B | +------------+------------+ |       123 | NULL | |       12345678 | NULL | |       65536 | NULL | |       1073741824 | NULL | |       2147483647 | NULL | | 2147483647 | 2147483647 | +------------+------------+ 6 rows in Set (0.00 sec)

It is clear whether the int has nothing to do with the number and the maximum value.

Then look at the numbers after char and varchar.

The code is as follows

Copy Code

Mysql> ALTER TABLE t add column C char (2); Query OK, 6 rows affected (0.17 sec) Records:6 duplicates:0 warnings:0 Mysql> ALTER TABLE t add column D varchar (2); Query OK, 6 rows affected (0.17 sec) Records:6 duplicates:0 warnings:0 Mysql> desc t; +-------+------------+------+-----+---------+-------+ | Field | type       | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | a     | int (1)       | yes  |     | null    |       | | b     | int (one)     | yes  |     | null    |       | | c     | char (2)     | yes  |     | null    |       | | d     | varchar (2) | yes  |     | null    |       | +-------+------------+------+-----+---------+-------+ 4 rows in Set (0.01 sec) Mysql> INSERT into t values (2147483647,2147483647, ' abc ', ' abc '); ERROR 1406 (22001): Data too long for column ' C ' at row 1 Mysql> INSERT into t values (2147483647,2147483647, ' ab ', ' abc '); ERROR 1406 (22001): Data too long for column ' d ' at row 1 Mysql> INSERT into t values (2147483647,2147483647, ' ab ', ' ab '); Query OK, 1 row affected (0.02 sec)

The number that is visible after char and varchar is the storage length.
So for Chinese characters, will there be more than the number of bytes and overflow the problem?

The code is as follows

Copy Code

Mysql> INSERT into t values (2147483647,2147483647, ' ab ', ' Medium '); Query OK, 1 row affected (0.02 sec) Mysql> select * from T; +------------+------------+------+------+ | a          | b           | c    | d    | +------------+------------+------+------+ |        123 |        NULL | NULL | NULL | |   12345678 |       NULL | NULL | NULL | |      65536 |       NULL | NULL | NULL | | 1073741824 |       NULL | NULL | NULL | | 2147483647 |       NULL | NULL | NULL | | 2147483647 | 2147483647 | NULL | NULL | | 2147483647 | 2147483647 | ab   | ab   | | 2147483647 | 2147483647 | ab   | +------------+------------+------+------+ 8 rows in Set (0.00 sec)

The Chinese text is also contained within 2 words.

The code is as follows

Copy Code

Mysql> INSERT into t values (2147483647,2147483647, ' ab ', ' Medium a '); ERROR 1406 (22001): Data too long for column ' d ' at row 1 Mysql> INSERT into t values (2147483647,2147483647, ' ab ', '? Series F '); Query OK, 1 row affected (0.05 sec) Mysql> select * from T; +------------+------------+------+------+ | A | B | C | D | +------------+------------+------+------+ |       123 | NULL | NULL | NULL | |       12345678 | NULL | NULL | NULL | |       65536 | NULL | NULL | NULL | |       1073741824 | NULL | NULL | NULL | |       2147483647 | NULL | NULL | NULL | | 2147483647 | 2147483647 | NULL | NULL | | 2147483647 | 2147483647 | AB | AB | | 2147483647 | 2147483647 | AB | The middle of the process | | 2147483647 | 2147483647 | AB |? f | +------------+------------+------+------+ 9 rows in Set (0.01 sec)

No more than one byte.
Look at the character encoding. MySQL character encoding consists of server, database, table, field level four.
MySQL Settings under Windows:

The code is as follows

Copy Code

Mysql> Show variables like "character%"; +--------------------------+--------------------------+ | variable_name | Value | +--------------------------+--------------------------+ | character_set_client | GBK | | character_set_connection | GBK | | Character_set_database | UTF8 | | Character_set_filesystem | binary | | Character_set_results | GBK | | Character_set_server | UTF8 | | Character_set_system | UTF8 | | Character_sets_dir | D:mysqlsharecharsets | +--------------------------+--------------------------+ 8 rows in Set (0.00 sec) Mysql> Show create TABLE t; T | CREATE TABLE ' t ' ( ' A ' int (1) DEFAULT NULL, ' B ' int (one) DEFAULT NULL, ' C ' char (2) DEFAULT NULL, ' d ' varchar (2) DEFAULT NULL Engine=innodb DEFAULT Charset=utf8

Linux under CentOS settings:

The code is as follows

Copy Code

Mysql> Show variables like "character%"; +--------------------------+----------------------------+ | variable_name | Value | +--------------------------+----------------------------+ | character_set_client | UTF8 | | character_set_connection | UTF8 | | Character_set_database | UTF8 | | Character_set_filesystem | binary | | Character_set_results | UTF8 | | Character_set_server | UTF8 | | Character_set_system | UTF8 | | Character_sets_dir | /opt/mysql/share/charsets/| +--------------------------+----------------------------+ 8 rows in Set (0.00 sec) Mysql> show full fields from T; Mysql> Show create DATABASE test; CREATE DATABASE ' test '/*!40100 DEFAULT CHARACTER SET UTF8 * *

Character encoding is UTF8,
Therefore, for UTF8 characters, the numbers behind varchar, whether in Chinese or English, indicate the corresponding number of words, do not worry about truncation.