Shulou(Shulou.com)06/01 Report--

1. Background

* string types supported by MySQL are CHAR, VARCHAR, binary, var binary, BLOB, text, and so on.

two。 Storage and value range required for string types

Types

Indicate whether the meaning of N has a character set maximum length CHAR (N) definition character is 255VARCHAR (N) variable length character is 16384BINARY (N) fixed length binary byte No 255VARBINARY (N)

Variable length binary byte No 16384TINYBLOB

Binary large object byte No 256BLOB

Binary large object byte No 16KMEDIUMBLOB

Binary large object byte No

16M

LONGBLOB

Binary large object byte No 4GTINYTEXT

The big object byte is 256TEXT

The big object byte is 16KMEDUIMBLOB

The big object byte is 16MLONGTEXT

Large object bytes are 4G

3. Difference between definition and variable length (CHAR VS VARCHAR)

Value CHAR (4) occupancy space VARHCAR (4) occupancy space''4 bytes''1 bytes

'ab''ab'4 bytes'ab'3 bytes'abcd''abcd'4 bytes'abcd'5 bytes'abcdefgh'

'abcd'4 bytes'abcd'5 bytes

4. Considerations related to string type

* when creating indexes on BLOB and TEXT columns, you must specify the length of the index prefix

* VARCHAR and VARBINARY must be optional in length

* BLOB and TEXT columns cannot have default values

* BLOB and TEXT columns are sorted using only the first max_sort_length bytes of that column

Mysql > show variables like 'max_sort_length';+-+-+ | Variable_name | Value | +-+-+ | max_sort_length | 1024 | +-+-+ 1 row in set (0.01 sec)

5. String types and collation

* View string related information [Default collation: default collation]

Mysql > show character set like 'utf8mb4' +-+ | Charset | Description | Default collation | Maxlen | +- -- + | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 | +-+ 1 row in set (0.01sec)

* View all utfmb4 collations

Mysql > show collation like 'utf8mb4%' +-+-+ | Collation | Charset | Id | Default | Compiled | Sortlen | + -+ | utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 | | utf8mb4_bin | utf8mb4 | 46 | Yes | 1 | utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 | | utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 | utf8mb4_latvian_ci | utf8mb4 | 226 | Yes | 8 | utf8mb4_romanian_ci | utf8mb4 | 227 | Yes | 8 | utf8mb4_slovenian_ci | utf8mb4 | 228 | Yes | 8 | utf8mb4_polish_ci | utf8mb4 | 229 | Yes | | | 8 | | utf8mb4_estonian_ci | utf8mb4 | 230 | | Yes | 8 | utf8mb4_spanish_ci | utf8mb4 | 231 | Yes | 8 | utf8mb4_swedish_ci | utf8mb4 | 232 | Yes | 8 | utf8mb4_turkish_ci | utf8mb4 | 233 | Yes | 8 | | utf8mb4_czech_ci | Utf8mb4 | 234 | | Yes | 8 | utf8mb4_danish_ci | utf8mb4 | 235 | Yes | 8 | utf8mb4_lithuanian_ci | utf8mb4 | 236 | Yes | 8 | utf8mb4_slovak_ci | utf8mb4 | 237 | Yes | 8 | utf8mb4_spanish3_ci | utf8mb4 | 238 | Yes | 8 | utf8mb4 _ roman_ci | utf8mb4 | 239 | | Yes | 8 | utf8mb4_persian_ci | utf8mb4 | 240 | | Yes | 8 | utf8mb4_esperanto_ci | utf8mb4 | 241 | Yes | 8 | utf8mb4_hungarian_ci | utf8mb4 | 242 | Yes | 8 | utf8mb4_sinhala_ci | utf8mb4 | 243 | | Yes | | | 8 | | utf8mb4_german2_ci | utf8mb4 | 244 | | Yes | 8 | utf8mb4_croatian_ci | utf8mb4 | 245 | Yes | 8 | utf8mb4_unicode_520_ci | utf8mb4 | 246 | | Yes | 8 | utf8mb4_vietnamese_ci | utf8mb4 | 247 | Yes | 8 | +-| -+-+ 26 rows in set (0.00 sec)

* modify the default collation

Mysql > set names utf8mb4 collate utf8mb4_bin;Query OK, 0 rows affected (0.00 sec) mysql > show character set like 'utf8mb4' +-+ | Charset | Description | Default collation | Maxlen | +- -- + | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 | +-+ 1 row in set (0.00 sec)

6. Summary

In order to demand-driven technology, there is no difference in technology itself, only in business.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.