Length of int and varchar in MySQL

What is the meaning of the number after the int In the MySQL field? What is the significance of the number after varchar?

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 |+----------+

It can be seen that int (1) does not represent a byte.
Will an error be reported if the number is longer?

mysql> insert into t values(1234567812345678);ERROR 1264 (22003): Out of range value for column 'a' at row 1mysql> insert into t values(2147483648);ERROR 1264 (22003): Out of range value for column 'a' at row 1mysql> insert into t values(2147483647);Query OK, 1 row affected (0.03 sec)

The maximum length of the int type is 2 ^ 31-1, plus the number of characters, it should be the length of four bytes.

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' at row 1mysql> insert into t values(2147483647,2147483647);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 can be seen that whether the number after int has nothing to do with the maximum value.

Let's look at the numbers after char and varchar.

mysql> alter table t add column c char(2);Query OK, 6 rows affected (0.17 sec)Records: 6  Duplicates: 0  Warnings: 0mysql> alter table t add column d varchar(2);Query OK, 6 rows affected (0.17 sec)Records: 6  Duplicates: 0  Warnings: 0mysql> desc t;+-------+------------+------+-----+---------+-------+| Field | Type       | Null | Key | Default | Extra |+-------+------------+------+-----+---------+-------+| a     | int(1)     | YES  |     | NULL    |       || b     | int(11)    | 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 1mysql> insert into t values(2147483647,2147483647,'ab','abc');ERROR 1406 (22001): Data too long for column 'd' at row 1mysql> insert into t values(2147483647,2147483647,'ab','ab');Query OK, 1 row affected (0.02 sec)

It can be seen that the number after char and varchar is the storage length.
So will there be a problem that the number of bytes exceeds the number of words in the Chinese text, and it will overflow?

Mysql> insert into T values (2147483647,2147483647, 'AB', 'sinochem '); query OK, 1 row affected (0.02 Sec) mysql> select * from T; + ------------ + ------ + | A | B | c | d | + ------------ + ------ + | 123 | null | 12345678 | null | null | 65536 | null | 1073741824 | null | 2147483647 | null | 2147483647 | 2147483647 | null | 2147483647 | 2147483647 | AB | 2147483647 | 2147483647 | AB | Sinochem | + ------------ + ---------- + ------ + 8 rows in SET (0.00 Sec)

The text is also contained in two characters.

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', 'command'); query OK, 1 row affected (0.05 Sec) mysql> select * from T; + ------------ + ------ + | A | B | c | d | + ------------ + ------ + | 123 | null | 12345678 | null | null | 65536 | null | 1073741824 | null | 2147483647 | null | 2147483647 | 2147483647 | null | 2147483647 | 2147483647 | AB | 2147483647 | 2147483647 | AB | Sinochem | 2147483647 | 2147483647 | AB | zookeeper | + ------------ + ---------- + ------ + 9 rows in set (0.01 Sec)

No more than one byte.
Take a look at the character encoding. MySQL character encoding consists of four levels: Server, database, table, and field.
MySQL settings in Windows:

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:\mysql\share\charsets\ |+--------------------------+--------------------------+8 rows in set (0.00 sec)mysql> show create table t; t     | CREATE TABLE `t` ( `a` int(1) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` char(2) DEFAULT NULL, `d` varchar(2) DEFAULT NULL ENGINE=InnoDB DEFAULT CHARSET=utf8

Centos settings in Linux:

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 */

The character encoding is utf8,
Therefore, for utf8 Chinese characters, the number after varchar, whether in Chinese or English, represents the corresponding number of words, do not worry about truncation.

Http://abloz.com/2011/09/19/mysql-in-int-and-varchar-length.html