Verify the Data Type in MySQL

Verify the Data Type in MySQL

CHAR

Char (M) M characters. The length is M * characters. The maximum length of M is 255.

The verification is as follows:

Mysql> create table t1 (name char (256) default charset = utf8; ERROR 1074 (42000): Column length too big for column 'name' (max = 255 ); use BLOB or TEXT insteadmysql> create table t1 (name char (255) default charset = utf8; Query OK, 0 rows affected (0.06 sec) mysql> insert into t1 values (repeat ('Integral ', 255); Query OK, 1 row affected (0.00 sec) mysql> select length (name), char_length (name) from t1; + -------------- + ----------------- + | length (name) | char_length (name) | + -------------- + ------------------- + | 765 | 255 | + ---------------- + ----------------- + 1 row in set (0.00 sec)

VARCHAR

VARCHAR (M), M is also a character, the length is M * character encoding length. The limit is special. The total length of a row cannot exceed 65535 bytes.

mysql> create table t1(name varchar(65535));ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBsmysql> create table t1(name varchar(65534));ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBsmysql> create table t1(name varchar(65533));ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBsmysql> create table t1(name varchar(65532));Query OK, 0 rows affected (0.08 sec) 

Note: The default Character Set of the preceding table is latin1 and the character length is 1 byte. Therefore, for varchar, you can only specify a maximum length of 65532 bytes.

If utf8 is specified, a maximum length of 21844 can be specified.

mysql> create table t1(name varchar(65532)) default charset=utf8;ERROR 1074 (42000): Column length too big for column 'name' (max = 21845); use BLOB or TEXT insteadmysql> create table t1(name varchar(21845)) default charset=utf8;ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBsmysql> create table t1(name varchar(21844)) default charset=utf8;Query OK, 0 rows affected (0.07 sec) 

Note: The maximum length of a row is 65535, only applicable to columns other than blob and text.

mysql> create table t1(name varchar(65528),hiredate datetime) default charset=latin1;ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBsmysql> create table t1(name varchar(65527),hiredate datetime) default charset=latin1;Query OK, 0 rows affected (0.01 sec) 

Indeed, datetime occupies 5 bytes.

TEXT, BLOB

mysql> create table t1(name text(255));Query OK, 0 rows affected (0.01 sec)mysql> create table t2(name text(256));Query OK, 0 rows affected (0.01 sec)mysql> show create table t1\G*************************** 1. row ***************************Table: t1Create Table: CREATE TABLE `t1` (`name` tinytext) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)mysql> show create table t2\G*************************** 1. row ***************************Table: t2Create Table: CREATE TABLE `t2` (`name` text) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec) 

The preceding output shows that text can be defined. If the range is smaller than 28 (that is, 256), tinytext is used. If the range is smaller than 216 (that is, 65536), text is used. If the range is smaller than 224, mediumtext, less than 232, longtext.

The preceding range is the number of bytes.

If the utf8 character set is defined, only 21845 characters can be inserted for text.

Mysql> create table t1 (name text) default charset = utf8; Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 values (repeat ('Integral ', 21846); ERROR 1406 (22001): Data too long for column 'name' at row 1 mysql> insert into t1 values (repeat ('Integral ', 21845 )); query OK, 1 row affected (0.05 sec)

DECIMAl

The official statement about Decimal is a bit confusing,

Values for DECIMAL (and NUMERIC) columns are represented using a binary format that packs nine decimal (base 10) digits into four bytes. Storage for the integer and fractional parts of each value are determined separately. Each multiple of nine digits requires four bytes, and the “leftover” digits require some fraction of four bytes. The storage required for excess digits is given by the following table. 

A corresponding table is provided.

The following are some of the explanations:

1. Each 9-bit request requires 4 bytes. the space required for the remaining digits is shown above.

2. the integer and decimal parts are calculated separately.

For example, Decimal (6, 5) can be seen from the definition, the integer occupies 1, the integer occupies 5, so a total of 1 + 3 = 4 bytes.

How to verify it? You can use InnoDB Table Monitor

How to start InnoDB Table Monitor, see: http://dev.mysql.com/doc/refman/5.7/en/innodb-enabling-monitors.html

mysql> create table t2(id decimal(6,5));Query OK, 0 rows affected (0.01 sec)mysql> create table t3(id decimal(9,0));Query OK, 0 rows affected (0.01 sec)mysql> create table t4(id decimal(8,3));Query OK, 0 rows affected (0.01 sec)mysql> CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB;Query OK, 0 rows affected, 1 warning (0.01 sec) 

The result is output to the error log.

View error logs:

For decimal (6, 5), the integer occupies 1 place, And the decimal occupies 5 places. The total space is 1 + 3 = 4 bytes.

For decimal (9, 0), the integer part is 9 bits, each 9 bits need 4 bytes, occupying a total of 4 bytes

For decimal (8, 3), the integer occupies 5 digits, and the decimal occupies 3 digits. The total space is 3 + 2 = 5 bytes.

So far, the common MySQL Data Type verification has been completed ~

For CHAR, VARCHAR, TEXT, and other character types, M specifies the number of characters. For CHAR, the maximum number of characters is 255. For VARCHAR, the maximum number of characters is related to the character set. If the character set is latin1, the maximum number of characters is 65532 (after all, each character occupies only one byte). For utf8, the maximum number of characters is 21844, because one character occupies three bytes. Essentially, VARCHAR is more limited by the row size (up to 65535 bytes ). TEXT is not limited by the row size, but is limited by its own definition.

Articles you may be interested in:

• MySQL Data Types
• Mysql LONGBLOB storage binary data (modification + debugging + sorting)
• MySQL Data Types
• Php + mysql authentication code
• Mysql returns all table names of a database, column name data type Remarks
• MySQL date data type and time type usage Summary
• Detailed description of MySQL Data Type varchar
• How to verify user permissions in MySQL
• How to Implement User authentication using PureFTP with MySQL