在用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