Validation of data types in Mysql _mysql

CHAR

Char (M) m character, length is m* character encoding length, M Max 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 instead
mysql> create table T1 (name char (255)) default Charset=utf8;
Query OK, 0 rows affected (0.06 sec)
mysql> insert INTO T1 values (repeat (' whole ', 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
| +--------------+-------------------+

VARCHAR

VARCHAR (m), M is also a character, length is m* character encoding length. Its limitations are special, and the total length of the line 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 are have to change some columns to TEXT or BLOBs mysql> 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 are have to change some columns to TEXT or BLOBs mysql> 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 are have to change some columns to TEXT or BLOBs mysql> create table T1 (name varchar (65532)); Query OK, 0 rows affected (0.08 sec) 

Note that the default character set for the above table is latin1 and the character length is 1 bytes, so for varchar, you can specify a maximum length of 65532 bytes.

If you specify UTF8, you can specify a maximum length of 21844

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 instead
mysql> 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 are have to change some columns to TEXT or BLOBs
mysql> create table T1 (name varchar (21844)) default Charset=utf8;

Note: The maximum length of the row is 65535, only for columns other than Blob,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 are have to change some columns to TEXT or BLOBs
mysql> create table T1 (name varchar (65527), HireDate datetime) DEFA Ult charset=latin1;

Indeed, datetime accounts for 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:t1
Create table:create Table ' t1 ' (
' name ' Tinytext
) Engine=innodb DEFAULT  Charset=latin1
1 row in Set (0.00 sec)
mysql> Show create TABLE t2\g
*************************** 1 row
table:t2
Create table:create Table ' T2 ' (
' name ' text
) engine= InnoDB DEFAULT charset=latin1

Through the above output can be seen text can define the length, if the range is less than 28 (that is, 256) is Tinytext, if the range is less than 216 (that is, 65536), then text, if less than 224, for Mediumtext, less than 232, for Longtext.

The above range is the number of bytes.

If you are defining the UTF8 character set, for text, you can actually insert only 21,845 characters

mysql> CREATE table T1 (name text) default Charset=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> insert INTO T1 values (repeat (' whole ', 21846));
ERROR 1406 (22001): Data too long for column ' name ' in row 1
mysql> insert into T1 values (repeat (' whole ', 21845));

DECIMAl

As for the decimal, the official statement is a bit round,

A corresponding table is also provided

For the interpretation of the above passage, there are the following points:

1. Each 9-bit requires 4 bytes, and the remaining number of digits is as shown above.

2. The integral part and the fractional part are calculated separately.

For example, in Decimal (6,5), the definition shows that integers are 1 bits and integers are 5 bits, so it takes up 1+3=4 bytes altogether.

How to verify it? Available via InnoDB Table Monitor

How to start InnoDB Table Monitor, refer to: 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;

The result is output to the error log.

To view the error log:

For decimal (6,5), the integer occupies 1 digits, and the decimal number occupies 5 bits, taking up space 1+3=4 bytes

For decimal (9,0), the integer part is 9 bits, 4 bytes per 9 bits, and a total space of 4 bytes

For decimal (8,3), the integer occupies 5 digits, and the decimal number occupies 3 bits, taking up space 3+2=5 bytes.

At this point, the common MySQL data type verification completed ~

For character types such as Char,varchar and text, 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, and if the character set is Latin1, then the maximum number of characters is 65532 (after all, each character occupies only one byte), and for UTF8, the maximum number of characters is 21844, because one character occupies three bytes. In essence, varchar is more constrained by row size (up to 65,535 bytes). For text, the row size is not restricted, but is limited by its own definition.