Description:
I'm porting data from oracle to mysql. This string is stored as varchar in both. I find I can't insert two characters by any means, 'load data infile' or 'insert' or anything. Anybody could be kind to give me a hand?
I dumped the hex in both DBs, and the hex string in oracle is longer than that in mysql, 'F0919790F097A284', which is at the end of the string and I can't port.
What's more, if I don't indicate utf8 while connecting to mysql(so it is latin1), the data could be inserted, but I can't use it because my program should read it as utf8, in which it is mess.
I'll try to attach some snapshot later. The two chars are at the end of the 'name' field.
How to repeat:
mysql> show create database testing;
+----------+--------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------+
| testing | CREATE DATABASE `testing` /*!40100 DEFAULT CHARACTER SET latin1 */ |
-------------I used two databases, whether latin1 or utf8 doesn't impact the result.
show create table test;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL,
`name` varchar(255) DEFAULT NULL,
`hello` varbinary(4000) DEFAULT NULL,
PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
load data in mysql:
load data infile '/tmp/id_13879610.txt' into table test fields terminated by ',' optionally enclosed by '"' (id,name,hello);
I also tried to edit one shell script as below:
#!/bin/bash
mysql -h121.14.36.21 -P6210 -uback_cen_slave -p'sdm4.vT3c' testing -e "set names utf8; insert into test (sid, name, hello) values (...);"
