mysql的varchar长度到底能插多少字符？

在用navicat迁移表结构，从oracle到MySQL时，注意如下坑：
1、如果varchar2(256)以上，则在mysql会自动用text取代，需要考虑手工修改字段类型为varchar(256)
ALTER TABLE DES_LOGIC_RESOURCE MODIFY REMARK VARCHAR(4000);
2、分区表自动变成普通表

对于varchar的长度设置，经过测试：

varchar(6)表示可以插入6个汉字，或6个字母数字，或汉字和字母数字共6个，是表示插入的字符数，不是字节数。

需要注意:这点和oracle的varchar2不同，oracle是字节数长度，不是字符长度，一个汉字占2个字节，所以长度6最多只能存3个汉字。

[root@lnpg ~]# mysql -ugistar -pxxxxxx -h192.168.207.143 -A -D resdb
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 256
Server version: 8.0.35 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> CREATE TABLE tmysql (
    ->   name varchar(6) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (1.76 sec)

mysql> insert into tmysql values('北京蒙迪艾尔');
insert into tmysql values('北京蒙迪艾尔');
Query OK, 1 row affected (0.21 sec)

mysql> insert into tmysql values('北京蒙迪艾尔');
Query OK, 1 row affected (0.04 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tmysql values('北京蒙迪艾尔a');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> show variables like '%char%';
+-------------------------------------------------+--------------------------------+
| Variable_name                                   | Value                          |
+-------------------------------------------------+--------------------------------+
| character_set_client                            | utf8mb4                        |
| character_set_connection                        | utf8mb4                        |
| character_set_database                          | utf8mb4                        |
| character_set_filesystem                        | binary                         |
| character_set_results                           | utf8mb4                        |
| character_set_server                            | utf8mb4                        |
| character_set_system                            | utf8mb3                        |
| character_sets_dir                              | /usr/share/mysql-8.0/charsets/ |
| validate_password.changed_characters_percentage | 0                              |
| validate_password.special_char_count            | 1                              |
+-------------------------------------------------+--------------------------------+
10 rows in set (4.94 sec)

mysql> insert into tmysql values('1234567');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> insert into tmysql values('123456');
Query OK, 1 row affected (0.02 sec)

mysql> insert into tmysql values('abcd1234');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> insert into tmysql values('abcdef');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tmysql values('abcde1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tmysql values('abcdef1');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> insert into tmysql values('abcde好');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tmysql values('abcde好1');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tmysql;
+--------------------+
| name               |
+--------------------+
| 北京蒙迪艾尔       |
| 北京蒙迪艾尔       |
| 123456             |
| abcdef             |
| abcde1             |
| abcde好            |
+--------------------+
6 rows in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.35    |
+-----------+
1 row in set (0.02 sec)

mysql> desc tmysql;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name  | varchar(6) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.39 sec)

mysql> 

看下Oracle的测试：汉字占用2个字节。

[oracle@lncs ~]$ sqlplus jyc/jyc

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 23 13:46:28 2024

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table toracle
  2    (
  3      NAME VARCHAR2(6)
  4     );

Table created.

SQL> insert into toracle values('北京蒙迪艾尔');
insert into toracle values('北京蒙迪艾尔')
                           *
ERROR at line 1:
ORA-12899: value too large for column "JYC"."TORACLE"."NAME" (actual: 12,
maximum: 6)


SQL> select length('北京蒙迪艾尔') from dual;                                        

LENGTH('北京蒙迪艾尔')
----------------------
                     6

SQL> insert into toracle values('蒙迪艾');                                     

1 row created.

SQL> select name ,length(name) from toracle;

NAME   LENGTH(NAME)
------ ------------
蒙迪艾            3

SQL> insert into toracle values('蒙迪艾尔');
insert into toracle values('蒙迪艾尔')
                           *
ERROR at line 1:
ORA-12899: value too large for column "JYC"."TORACLE"."NAME" (actual: 8,
maximum: 6)


SQL> insert into toracle values('1234567');
insert into toracle values('1234567')
                           *
ERROR at line 1:
ORA-12899: value too large for column "JYC"."TORACLE"."NAME" (actual: 7,
maximum: 6)


SQL> insert into toracle values('123456');

1 row created.

SQL> insert into toracle values('12345蒙');
insert into toracle values('12345蒙')
                           *
ERROR at line 1:
ORA-12899: value too large for column "JYC"."TORACLE"."NAME" (actual: 7,
maximum: 6)


SQL> insert into toracle values('1234蒙');

1 row created.

SQL> commit;

Commit complete.

SQL> select length('1234蒙') from dual;         

LENGTH('1234蒙')
----------------
               5

SQL> select length('12345') from dual;       

LENGTH('12345')
---------------
              5

SQL> select length('好12345') from dual;

LENGTH('好123455')
------------------
                 7

SQL> select length('好1234') from dual;

LENGTH('好1234')
----------------
               5