oracle数据库相关表导入到mysql遇到的错误及相关解决措施

根据pdm表，改变成mysql版本。然后生成sql文件，在Navicat中遇到很多问题。

首先总结下问题：

插入数据时，没遇到有汉字的表数据就插入不成功，怀疑mysql的编码问题。

oracle自带的函数如to_date在mysql中不支持

oracle中对varchar的长度定义在mysql中会偏大，转为text类型

主键重复写入问题

 

1 插入数据时，没遇到有汉字的表数据就插入不成功，怀疑mysql的编码问题。解决措施：新建数据库时指定编码：create database jcs character set utf8；

  MySQL的默认编码是Latin1，不支持中文，要支持中午需要把数据库的默认编码修改为gbk或者utf8。

 

（1）、需要以root用户身份登陆才可以查看数据库编码方式(以root用户身份登陆的命令为：>mysql -u root –p,之后两次输入root用户的密码)，查看数据库的编码方式命令为:

>show variables like 'character%';
+--------------------------+----------------------------+
| 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/ |
+--------------------------+----------------------------+

从以上信息可知数据库的编码为 latin1，需要修改为gbk或者是utf8；

其中， character_set_client为客户端编码方式；character_set_connection为建立连接使用的编码；character_set_database数据库的编码；

character_set_results结果集的编码；

character_set_server数据库服务器的编码；

只要保证以上四个采用的编码方式一样，就不会出现乱码问题。

另一个查看数据库编码的命令：

>show variables like ‘collation%’;

（2）、 linux系统下，修改MySQL数据库默认编码的步骤为：

ü 停止 MySQL的运行
/etc/init.d/mysql start (stop) 为启动和停止服务器

ü MySQL主配置文件为 my.cnf，一般目录为/etc/mysql

var/lib/mysql/ 放置的是数据库表文件夹，这里的 mysql相当于windows下mysql的date文件夹

ü 当我们需要修改 MySQL数据库的默认编码时，需要编辑my.cnf文件进行编码修改,在linux下修改mysql的配置文件my.cnf，文件位置默认/etc/my.cnf文件

找到客户端配置[client] 在下面添加
default-character-set=utf8 默认字符集为utf8
在找到[mysqld] 添加
default-character-set=utf8 默认字符集为utf8
init_connect='SET NAMES utf8' （设定连接mysql数据库时使用utf8编码，以让mysql数据库为utf8运行）

修改好后，重新启动mysql 即可，重新查询数据库编码可发现编码方式的改变：

>show variables like 'character%';
+--------------------------+----------------------------+
| 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/share/mysql/charsets/ |
+--------------------------+----------------------------+

此方法用于标准 mysql版本同样有效，对于/etc/my.cnf文件，需要从mysql/support-files的文件夹cp my-large.cnf一份到/etc/my.cnf

（3）、 windows系统下可以删除MySQL数据库，并重新安装，在安装过程中可以直接用Ｍysql Server Instance Config Wizard 进行设置

（4）、当MySQL数据库服务器已经有数据不适合删除重装时，可以个别指定数据库的编码方式。MySQL指定编码的方式是非常灵活并多样化的，可以指定表级别的编码，行级别编码，甚至可以指定字段级别的编码。

以下示例给出创建数据库时指定编码的两种方式：

1）CREATE  DATABASE  ms_db  CHARACTER SET  utf8  COLLATE utf8_general_ci；

2）create  database  if  not  exists  netctoss  default character set utf8;

（5）、如果你采用的是外部接入的方式，可以在连接中确定请求的编码格式如：jdbc:mysql://localhost:3306 /mysql?useUnicode=true&characterEncoding=utf-8(注意：不要出现任何空格，否则出错)

（6）、执行脚本：指定编码格式set names gbk(注意，不是UTF-8)可以修改

执行前：

执行后：

从执行命令前后可知， set names gbk只可以修改character_set_client、character_set_connection、 character_set_results的编码方式，并且这种修改是窗口级别的，只针对本窗口有效，打开另外一个窗口修改无效。也可发现数据库底层的编码方式没有改变，插入数据后还是以utf8编码方式保持。

 

2 遇到的问题是oacle中的to_date函数在mysql中不适用，解决措施：改为data_format

 

 

oracle                                       -->                             mysql

to_char(sysdate,'yyyy-mm-dd')-->date_format(sysdate(),'%Y-%m-%d');
to_date(sysdate,'yyyy-mm-dd')-->STR_TO_DATE(sysdate(),'%Y-%m-%d');

1.oracle

（1）使用TO_CHAR函数处理数字

          TO_CHAR(number, '格式')   

          TO_CHAR(salary,’$99,999.99’);

  （2）使用TO_CHAR函数处理日期
          TO_CHAR(date,’格式’);
          to_char(sysdate,'q')   季  
          to_char(sysdate,'yyyy')年  
          to_char(sysdate,'mm')月  
          to_char(sysdate,'dd')日  
          to_char(sysdate,'d')星期中的第几天
          to_char(sysdate,'DAY')星期几
          to_char(sysdate,'ddd')一年中的第几天

（3）TO_NUMBER
         使用TO_NUMBER函数将字符转换为数字
         TO_NUMBER(char[, '格式'])
（4） TO_DATE
         使用TO_DATE函数将字符转换为日期
         TO_DATE(char[, '格式'])

------------------------------------------------------------------------------------------

2.sql

   1)类型转换：Cast（）类型转换，例：Cast(@startdate as datetime)，Cast(name as nvarchar)
   2)日期截取：Datepart()返回代表指定日期的指定日期部分的整数，例：
         Datepart(Dd,@startdate)--取日，
         Datepart(Month,@startdate)--取月，
         Datepart(Yy,@startdate)--取年

   3)日期操作：Dateadd（）在向一个日期加上一个段时间的基础上，返回新的datetime类型。例：加2天 Dateadd（day,2,'2009-6-1'）返回：2009-6-3 00:00:00.000

------------------------------------------------------------------------------------------

3.mysql

日期格式转换：date_format(date,format), time_format(time,format)

1)使用DATE_FORMAT(数据库字段,'%Y%m%d')将时间类型字段格式为"yyyy-mm-dd"字符串格式

2)str_to_date(数据库字段,'%m.%d.%Y')将字符串类型字段格式为"yyyy-mm-dd"时间格式

3)mysql> select date_format('2008-08-08 22:23:00', '%W %M %Y');

+------------------------------------------------+
| date_format('2008-08-08 22:23:00', '%W %M %Y') |
+------------------------------------------------+
| Friday August 2008 |
+------------------------------------------------+

mysql> select date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s');

+----------------------------------------------------+
| date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s') |
+----------------------------------------------------+
| 20080808222301 |
+----------------------------------------------------+

mysql> select time_format('22:23:01', '%H.%i.%s');

+-------------------------------------+
| time_format('22:23:01', '%H.%i.%s') |
+-------------------------------------+
| 22.23.01 |
+-------------------------------------+

MySQL 日期、时间转换函数：date_format(date,format), time_format(time,format) 能够把一个日期/时间转换成各种各样的字符串格式。它是 str_to_date(str,format) 函数的 一个逆转换。

4)

TO_NUMBER用cast就可,但要填适当的类型,示例:

select cast(11 as unsigned int) /*整型*/

select cast(11 as decimal(10,2)) /*浮点型*/

 

 3 到的问题是Duplicate entry '402898ad35509a9f0135509da1db0004' for key 'PRIMARY'即数据库中已经有相应的主键，造成主键重复填入，解决措施更改主键，或者是去数据库中查看已经有的主键。和设置主键增长类型是自增长。

4 遇到的错误时Incorrect integer value: '' for column 'new_tag_3' at row 1。即mysql中的列虽然可以为空，但必须插入null，不能为‘’。

 

insert into log values('','admin','31','physics')
这样写就会报错：
Incorrect integer value: '' for column 'id' at row 1
这种应该是版本问题，尤其是虚拟主机。
把''改成null不会出错：
insert into log values(null,'admin','31','physics')
insert into log values(' ','admin','31','physics')//加个空格

这种问题一般mysql 5.x上出现。我用的mysql5.1，后面查询得知新版本mysql对空值插入有"bug",要在安装mysql的时候去除默认勾选的enable strict SQL mode。如果已经安装好了的，就在my.ini中查找sql-mode，默认为sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"，将其修改为sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"，重启mysql后即可。

发现以前的程序居然不能正常运行,提示信息如下:
Database error: Invalid SQL: INSERT INTO Survey_UserHistory(userId,jobId,type,action,startTime,endTime) VALUES(’17′,”,’User’,’Login’,”,’2008-11-23 14:33:56′)
MySQL error: 1366 (Incorrect integer value: ” for column ‘jobId’ at row 1)
Session halted.

第一时间的反应是跟mysql5.0新加的sql-mode有关系,因为前一阵子刚刚看过手册.果然,作了如下更改后就不再提示了.

修改 my.ini 文件.

# Set the SQL mode to strict
sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

改为:

# Set the SQL mode to strict
sql-mode=”NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

5

遇到的问题是：Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs。

对于在oracele中整理的的varchar2（4000）在mysql的varchar类型的字段长度定义，MySQL要求一个行的定义长度不能超过65535，所以需要把相应的大字段修改为text或者是blob。