Data Types of MySQL notes

Balanced data

The fixed-length or variable-length string can be used for string type data. The difference is that a string of the fixed length is filled with spaces to the right to ensure the same number of bytes. The variable-length string does not need to be filled to the right, and the number of all bytes is variable.

Char fixed length, fixed byte length, maximum length of 255 bytes, you can use char (20) to limit the length, fast query, but not flexible

Varchar variable-length type. The length of the byte varies with the amount of data. Up to 65535 bytes can be stored. varchar (20) can be limited in length and the query speed is lower than that of char, but it is flexible to use.

If you want to store text or documents, you need to use text data, including four types

Tinytext 255 bytes

Text 65535 bytes

Mediumtext 16777215 bytes

Longtext 4294967295 bytes

Only the first 1024 bytes are used for group sorting or comparison.

Character Set

For the Latin series, such as English, each letter only needs one byte for storage. Other languages, such as Chinese, contain a large number of characters. The storage of each character requires multiple bytes, therefore, such character sets are called multi-character sets.

In mysql, you can use the show cahracter set statement to view character sets.
Copy codeThe Code is as follows:
Mysql> show character set;
+ ---------- + ----------------------------- + --------------------- + -------- +
| Charset | Description | Default collation | Maxlen |
+ ---------- + ----------------------------- + --------------------- + -------- +
| Big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| Dec8 | DEC West European | dec8_swedish_ci | 1 |
| Cp850 | DOS West European | cp850_general_ci | 1 |
| Hp8 | HP West European | hp8_english_ci | 1 |
| Koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| Latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| Latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| Swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| Ascii | us ascii | ascii_general_ci | 1 |
| Ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| Sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| Hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| Tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| Euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| Koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| Gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| Greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| Cp1250 | Windows Central European | cp1250_general_ci | 1 |
| Gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| Latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| Armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| Utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| Ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| Cp866 | DOS Russian | cp866_general_ci | 1 |
| Keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| Macce | Mac Central European | macce_general_ci | 1 |
| Macroman | Mac West European | macroman_general_ci | 1 |
| Cp852 | DOS Central European | cp852_general_ci | 1 |
| Latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| Utf8mb4 | Unicode of the UTF-8 | utf8mb4_general_ci | 4 |
| Cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| Utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| Cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| Cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| Utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| Binary | Binary pseudo charset | binary | 1 |
| Geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| Cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| Eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+ ---------- + ----------------------------- + --------------------- + -------- +
Rows in set (0.00 sec)

If the Maxlen column is greater than 1, it indicates that this row is a multi-character set. For example, if the value of gb2312 is greater than 1, a maximum of two bytes are required for storing a Chinese character, and a maximum of three bytes are required for utf8.

Numeric data

Integer Data

Floating Point Data

Numeric data can be defined as unsigned, and the range of an integer can be changed. For floating-point data, the value cannot be negative.

 

Time Data