Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL Basic tutorial: About varchar (N)     - Learning Linux coding style (Programming)

- The OpenGL ES GLFW window structures (Programming)

- Ubuntu manually set the DSL broadband connection (Linux)

- Linux systems use logwatch log file monitoring (Linux)

- MySQL function: group_concat () function (Database)

- Linux environment variable settings methods and differences (Linux)

- Linux Thread Synchronization (Programming)

- How to configure FirewallD in RHEL / CentOS 7 and Fedora in (Linux)

- Use MongoDB C # MongoDB official driving operation (Database)

- Log4cplus logging facility configuration, installation, testing (Linux)

- IPTABLES configuration steps under Linux (Linux)

- RHEL7 unattended automatic installation DHCP + TFTP + SYSLINUX + TFTP + Kickstart (Linux)

- Nginx Beginner Guide (Server)

- Linux environment RabbitMQ installation and monitoring of plug-in installation (Linux)

- MySQL completely uninstall and install Configuring Character Sets under Linux (Database)

- Optimized to minimize the installation of CentOS 5.8 (Linux)

- Use Hexo quickly build and deploy a blog to Github (Server)

- Android Studio utility plug organize, create sharp artifact (Programming)

- Linux System Getting Started Learning: Repair fatal error openssl aes h no such file or directory (Linux)

- Sublime Text 3 best features, plug-ins and settings (Linux)

 
         
  MySQL Basic tutorial: About varchar (N)
     
  Add Date : 2018-11-21      
         
         
         
  One. Introduction
  varchar (N) N What does it mean, how many Chinese characters can be stored? Belonging commonplace problem, and today is a developed colleague asked me about this issue, simply write an article specific about.

 Two theoretical knowledge
 MySQL has always been first explain the definition of varchar version of:
  Version 4.0 or less, varchar (50), referring to the 50 bytes, if the store UTF8 characters, can store 16 (3 bytes for each Chinese)
  5.0 or later, varchar (50), referring to the 50 characters, whether stored numbers, letters or UTF8 Chinese (Chinese 3 bytes each), can store 50
 Storage limits
  The need for additional storage byte characters occupy length: less than 255 bytes to a larger than 255 bytes will have two
 Coded limit
  gbk: Each character occupies two bytes Up
  utf8: Each character occupies a maximum of three bytes
  utf8mb4 each character up to 4 bytes, Chinese accounted for 3 bytes, emoji emoticons 4 bytes
 Length limit
  MySQL definition line length can not exceed 65535, this value limits the number of columns, such as char (128) utf8 character set, up to 65535 / (128 * 3) = 170 characters.

Three test
 Environment Server version: 5.6.26-74.0-log Percona Server
mysql> create table t1
    -> (Id int NOT NULL AUTO_INCREMENT primary key,
    -> Name varchar (10)
    ->) Engine = innodb default charset = utf8mb4;
Query OK, 0 rows affected (0.01 sec)
mysql> create table t2
    -> (Id int NOT NULL AUTO_INCREMENT primary key,
    -> Name varchar (10)
    ->) Engine = innodb default charset = utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> create table t3
    -> (Id int NOT NULL AUTO_INCREMENT primary key,
    -> Name varchar (10)
    ->) Engine = innodb default charset = gbk;
Query OK, 0 rows affected (0.01 sec)
utf8mb4 character set
mysql> insert into t1 (name) values ​​( 'abcdfeghi');
 Query OK, 1 row affected (0.00 sec)
 mysql> insert into t1 (name) values ​​( 'abcdfeghij');
 Query OK, 1 row affected (0.00 sec)
 mysql> insert into t1 (name) values ​​( 'abcdfeghijk');
 Query OK, 1 row affected, 1 warning (0.00 sec)
 mysql> insert into t1 (name) values ​​( '12,345,678 ninety');
Query OK, 1 row affected (0.00 sec)
 mysql> insert into t1 (name) values ​​( '123,456,789 11');
Query OK, 1 row affected, 1 warning (0.00 sec)
 mysql> show warnings;
 + --------- + ------ + -------------------------------- ----------- +
 | Level | Code | Message |
 + --------- + ------ + -------------------------------- ----------- +
 | Warning | 1265 | Data truncated for column 'name' at row 1 |
 + --------- + ------ + -------------------------------- ----------- +
 1 row in set (0.00 sec)
 mysql> insert into t1 (name) values ​​( '0123456789');
 Query OK, 1 row affected (0.00 sec)
 mysql> insert into t1 (name) values ​​( '01234567890');
 Query OK, 1 row affected, 1 warning (0.00 sec)
 mysql> select id, name, length (name), char_length (name) from t1;
 + ---- + -------------------------------- + ----------- --- + ------------------- +
 | Id | name | length (name) | char_length (name) |
 + ---- + -------------------------------- + ----------- --- + ------------------- +
 | 1 | abcdfeghi | 9 | 9 |
 | 2 | abcdfeghij | 10 | 10 |
 | 3 | abcdfeghij | 10 | 10 |
 | 4 | 12,345,678 ninety | 30 | 10 |
 | 5 | 12345678 ninety | 30 | 10 |
 | 6 | 0123456789 | 10 | 10 |
 | 7 | 0123456789 | 10 | 10 |
 + ---- + -------------------------------- + ----------- --- + ------------------- +
 7 rows in set (0.00 sec)
utf8 character set
mysql> insert into t2 (name) values ​​( 'abcdfeghi');
 Query OK, 1 row affected (0.00 sec)
 mysql> insert into t2 (name) values ​​( 'abcdfeghij');
 Query OK, 1 row affected (0.00 sec)
 mysql> insert into t2 (name) values ​​( 'abcdfeghijk');
 Query OK, 1 row affected, 1 warning (0.00 sec)
 mysql> insert into t2 (name) values ​​( '12,345,678 ninety');
Query OK, 1 row affected (0.00 sec)
 mysql> insert into t2 (name) values ​​( '123,456,789 11');
Query OK, 1 row affected, 1 warning (0.00 sec)
 mysql> insert into t2 (name) values ​​( '0123456789');
 Query OK, 1 row affected (0.00 sec)
 mysql> insert into t2 (name) values ​​( '01234567890');
 Query OK, 1 row affected, 1 warning (0.00 sec)
 mysql> select id, name, length (name), char_length (name) from t2;
 + ---- + -------------------------------- + ----------- --- + ------------------- +
 | Id | name | length (name) | char_length (name) |
 + ---- + -------------------------------- + ----------- --- + ------------------- +
 | 1 | abcdfeghi | 9 | 9 |
 | 2 | abcdfeghij | 10 | 10 |
 | 3 | abcdfeghij | 10 | 10 |
 | 4 | 12,345,678 ninety | 30 | 10 |
 | 5 | 12345678 ninety | 30 | 10 |
 | 6 | 0123456789 | 10 | 10 |
 | 7 | 0123456789 | 10 | 10 |
 + ---- + -------------------------------- + ----------- --- + ------------------- +
 7 rows in set (0.00 sec)
gbk Character Set
mysql> insert into t3 (name) values ​​( 'abcdfeghi');
 Query OK, 1 row affected (0.00 sec)
 mysql> insert into t3 (name) values ​​( 'abcdfeghij');
 Query OK, 1 row affected (0.00 sec)
 mysql> insert into t3 (name) values ​​( 'abcdfeghijk');
 Query OK, 1 row affected, 1 warning (0.00 sec)
 mysql> insert into t3 (name) values ​​( '12,345,678 ninety');
Query OK, 1 row affected (0.01 sec)
 mysql> insert into t3 (name) values ​​( '123,456,789 11');
Query OK, 1 row affected, 1 warning (0.00 sec)
 mysql> insert into t3 (name) values ​​( '0123456789');
 Query OK, 1 row affected (0.00 sec)
 mysql> insert into t3 (name) values ​​( '01234567890');
 Query OK, 1 row affected, 1 warning (0.00 sec)
 mysql> select id, name, length (name), char_length (name) from t3;
 + ---- + -------------------------------- + ----------- --- + ------------------- +
 | Id | name | length (name) | char_length (name) |
 + ---- + -------------------------------- + ----------- --- + ------------------- +
 | 1 | abcdfeghi | 9 | 9 |
 | 2 | abcdfeghij | 10 | 10 |
 | 3 | abcdfeghij | 10 | 10 |
 | 4 | 12,345,678 ninety | 20 | 10 |
 | 5 | 12345678 ninety | 20 | 10 |
 | 6 | 12,345,678 ninety | 20 | 10 |
 | 7 | 0123456789 | 10 | 10 |
 | 8 | 0123456789 | 10 | 10 |
 + ---- + -------------------------------- + ----------- --- + ------------------- +
 8 rows in set (0.00 sec)
    As can be seen from the above test, the current units of length versions varchar (N) is defined by the character, length (str) str indicates the number of bytes occupied, char_length (str) represents the number of characters in str occupied.
 No matter what character set for the numbers and letters are only takes a character, also occupies one byte. The Chinese characters due to different characters and different.

Four summary
  Back to the beginning of the article to answer questions varchar (N) the number of Chinese characters can be stored. The answer is that in a future version 5.0 varchar (N) can be stored N characters.
  Character Set aside, if a row all varchar type, maximum length of 65535 bytes.
 Line length is calculated as follows:
row length = 1
            + (Sum of column lengths)
            + (Number of NULL columns + delete_flag + 7) / 8
            + (Number of variable-length columns)
  For MyISAM, an extra 1 bit to record whether the value is NULL; For InnoDB, there is no difference
 For row_format is fixed, delete_flag 1; for row_format = dynamic, delete_flag to 0

According to this formula, we will be able to answer at the beginning of the maximum of N: (65535-1-2) / 3
Minus one is because the actual storage from the 2 byte
 Save 2 due to the actual length of the list is stored in the character length
 In addition to 3 because the utf8 encoding limit
 Again one:
create table t4 (c int, c2 char (30), c3 varchar (N)) charset = utf8;
 The maximum value of N: (65535-1-2-4-30 * 3) / 3
Where N is the maximum value (65535-1-2-4-30 * 3) / 3 = 21812
Minus 1 and minus 2 are the same;
Minus 4 because the type int c 4 bytes;
Save 30 * 3 because char (30) takes up 90 bytes, the encoding is utf8.
 If varchar b exceeds the above rules are strong turn into text type, each field occupies a defined length of 11 bytes, of course, this is not "varchar" the
     
         
         
         
  More:      
 
- After restarting network services, DNS address failure (Linux)
- Free compiler install MySQL-5.6.14 (Database)
- Six Ways to view slides can be implemented Android (Programming)
- Linux network cut package is not fully defragment (Linux)
- Oracle 11g user rights management study notes (Database)
- Getting Started with Linux system to learn: how to get the process ID (PID) in the script (Linux)
- Android Studio Personalization (Linux)
- Linux command binlog by filtering out the complete SQL statement (Database)
- To create a secure network firewall with iptables Under Linux (Linux)
- Ubuntu installation 2.10.x version of Scala (Linux)
- JavaScript common functions summary (Programming)
- Eclipse distributed management using GitHub project development (Linux)
- Linux operating tips: Can not open file for writing or operation not permitted solution (Linux)
- Installation under Linux Mint system guidelines for Gtk (Linux)
- Linux virtual machine how to access the Internet in a virtual machine when using NAT mode (Linux)
- How to use static, class, abstract method in Python (Programming)
- Disk Management LVM (Linux)
- Linux, Chrome browser font settings beautification (Linux)
- MySQL High Availability plan several options (Database)
- Nginx request processing (Server)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.