CHAR_LENGTH() Return number of characters(字符) in argument(参数)
LENGTH()  Return the length of a string(字符串) in bytes(字节)

https://dev.mysql.com/doc/refman/5.5/en/string-functions


官方说明,CHAR_LENGTH() 返回变量中字符的个数,LENGTH() 返回一个字符串占多少字节。
个人英语比较马虎,所以不知道翻译对不对,凑合看。
结论:

gbk-gbk 一个中文字符也是一个字符,一个汉子占两个字节,所以是 5 7
gbk - utf8  不能直接转换,需通过unicode转码,转码后就是 7 7
gbk- latin1 可直接转换,转换为单字节字符,转码后是 7 7
utf8(一个中文三个字节)---gbk: 直接转换,尾部奇数字节截断(中文个数*3/2+英文字符个数) 9  9
utf8(一个中文三个字节)---utf8: 不用转换,就是 5  9
utf8--------latin1: 直接转换,单字节字符  就是 9 9

 

select char_length('世界sad'),length('世界sad');的结果跟两个因素有关:
客户端(linux默认utf8,windows默认GB2312,可通过securecrt 模拟),mysql字符编码设置(主要是client,connection和result,set names xxx)

测试1:
mysql version:5.6.17 windows 7 平台

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> select char_length('世界sad'),length('世界sad');
+------------------------+-------------------+
| char_length('世界sad') | length('世界sad') |
+------------------------+-------------------+
| 7 | 7 |
+------------------------+-------------------+1 row in set (0.00 sec)

mysql> show variables like 'char%';
+--------------------------+-----------------------------------------------+
| Variable_name | Value |
+--------------------------+-----------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | D:\wamp\bin\mysql\mysql5.6.17\share\charsets\ |
+--------------------------+-----------------------------------------------+8 rows in set (0.00 sec)

mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)

mysql> select char_length('世界sad'),length('世界sad');
+------------------------+-------------------+
| char_length('世界sad') | length('世界sad') |
+------------------------+-------------------+
| 5 | 7 |
+------------------------+-------------------+1 row in set (0.00 sec)

mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> select char_length('世界sad'),length('世界sad');
+------------------------+-------------------+
| char_length('世界sad') | length('世界sad') |
+------------------------+-------------------+
| 7 | 7 |
+------------------------+-------------------+1 row in set (0.00 sec)

 

wKioL1XbRSqieeNHAAXkG2oMkKg105.jpg



测试2:
mysql 5.5   tlinux 1.2/centos 6.x

mysql> set names utf8;Query OK, 0 rows affected (0.00 sec)

mysql>  select char_length('世界sad'),length('世界sad');+--------------------------+---------------------+| char_length('世界sad') | length('世界sad') |
+--------------------------+---------------------+|                        5 |                   9 |
+--------------------------+---------------------+1 row in set (0.00 sec)

mysql> set names gbk;Query OK, 0 rows affected (0.00 sec)

mysql>  select char_length('世界sad'),length('世界sad');+--------------------------+---------------------+| char_length('世界sad') | length('世界sad') |
+--------------------------+---------------------+|                        6 |                   9 |
+--------------------------+---------------------+1 row in set (0.00 sec)

mysql> set names latin1;Query OK, 0 rows affected (0.00 sec)

mysql>  select char_length('世界sad'),length('世界sad');+--------------------------+---------------------+| char_length('世界sad') | length('世界sad') |
+--------------------------+---------------------+|                        9 |                   9 |
+--------------------------+---------------------+1 row in set (0.00 sec)

mysql>

 

测试3: mysql 5.6   tlinux1.2

elcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1Server version: 5.6.25 Source distribution

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select char_length('世界sad'),length('世界sad');
+--------------------------+---------------------+
| char_length('世界sad')   | length('世界sad')   |
+--------------------------+---------------------+
|                        5 |                   9 |
+--------------------------+---------------------+1 row in set (0.00 sec)

mysql>  show variables like 'char%';
+--------------------------+-----------------------------------------+
| Variable_name            | Value                                   |
+--------------------------+-----------------------------------------+
| character_set_client     | utf8                                    |
| character_set_connection | utf8                                    |
| character_set_database   | utf8                                    |
| character_set_filesystem | binary                                  |
| character_set_results    | utf8                                    |
| character_set_server     | utf8                                    |
| character_set_system     | utf8                                    |
| character_sets_dir       | /usr/local/mysql-5.6.25/share/charsets/ |
+--------------------------+-----------------------------------------+8 rows in set (0.00 sec)

mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)

mysql>  show variables like 'char%';
+--------------------------+-----------------------------------------+
| Variable_name            | Value                                   |
+--------------------------+-----------------------------------------+
| character_set_client     | gbk                                     |
| character_set_connection | gbk                                     |
| character_set_database   | utf8                                    |
| character_set_filesystem | binary                                  |
| character_set_results    | gbk                                     |
| character_set_server     | utf8                                    |
| character_set_system     | utf8                                    |
| character_sets_dir       | /usr/local/mysql-5.6.25/share/charsets/ |
+--------------------------+-----------------------------------------+8 rows in set (0.00 sec)

mysql> select char_length('世界sad'),length('世界sad');
+--------------------------+---------------------+
| char_length('世界sad')   | length('世界sad')   |
+--------------------------+---------------------+
|                        6 |                   9 |
+--------------------------+---------------------+1 row in set (0.02 sec)

mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)

mysql>  show variables like 'char%';
+--------------------------+-----------------------------------------+
| Variable_name            | Value                                   |
+--------------------------+-----------------------------------------+
| character_set_client     | latin1                                  |
| character_set_connection | latin1                                  |
| character_set_database   | utf8                                    |
| character_set_filesystem | binary                                  |
| character_set_results    | latin1                                  |
| character_set_server     | utf8                                    |
| character_set_system     | utf8                                    |
| character_sets_dir       | /usr/local/mysql-5.6.25/share/charsets/ |
+--------------------------+-----------------------------------------+8 rows in set (0.00 sec)

mysql> select char_length('世界sad'),length('世界sad');
+--------------------------+---------------------+
| char_length('世界sad')   | length('世界sad')   |
+--------------------------+---------------------+
|                        9 |                   9 |
+--------------------------+---------------------+1 row in set (0.00 sec)

mysql>

 

测试4:

mariadb 5.5  tlinux 2.0/centos 7

[root@tlinux2_ops01      ~]#  rpm -qa |grep mariadbmariadb-devel-5.5.41-2.el7_0.x86_64
mariadb-libs-5.5.41-2.el7_0.x86_64
mariadb-server-5.5.41-2.el7_0.x86_64
mariadb-embedded-devel-5.5.41-2.el7_0.x86_64
mariadb-embedded-5.5.41-2.el7_0.x86_64
mariadb-test-5.5.41-2.el7_0.x86_64
mariadb-5.5.41-2.el7_0.x86_64
mariadb-bench-5.5.41-2.el7_0.x86_64
[root@tlinux2_ops01      ~]# mysqlWelcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 58Server version: 5.5.41-MariaDB-log MariaDB Server

Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show variables like 'char%';show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+8 rows in set (0.00 sec)

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+8 rows in set (0.00 sec)

MariaDB [(none)]> select char_length('世界sad'),length('世界sad');
+--------------------------+---------------------+
| char_length('世界sad') | length('世界sad') |
+--------------------------+---------------------+
|                        9 |                   9 |
+--------------------------+---------------------+1 row in set (0.00 sec)

MariaDB [(none)]> set names gbk;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select char_length('世界sad'),length('世界sad');
+--------------------------+---------------------+
| char_length('世界sad') | length('世界sad') |
+--------------------------+---------------------+
|                        6 |                   9 |
+--------------------------+---------------------+1 row in set (0.00 sec)

MariaDB [(none)]> set names utf8;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select char_length('世界sad'),length('世界sad');
+--------------------------+---------------------+
| char_length('世界sad') | length('世界sad') |
+--------------------------+---------------------+
|                        5 |                   9 |
+--------------------------+---------------------+1 row in set (0.00 sec)

MariaDB [(none)]>

 

测试5:
windows系统(终端设置为utf8) mysql 5.6 
chcp 65001 

wKiom1XbQxXgg0W5AAbRB17OJDU558.jpg