Category: Programming

• When importing hex string data, mysql load data will import them successfully but treat them as string without 0x indicating it as a hex string. For example, the imported result seen below: (description column, type longblob)

MyTable
pid  count  description(longblob datatype)
1,   1,     123456FF
2,   1,     aabbcc
3,   1,     ddeeff
instead of 

1,   1,     0x123456FF
2,   1,     0xaabbcc
3,   1,     0xddeeff

And even we manually add 0x to these hex string, when viewing it in mysql's Open Value in Editor, it shows them as literal string and if we save it to file, it is a literal string instead of the actual value the hex string represent so how do we resolve this issue.

1- we can use SET within Load Data Infile:
https://dev.mysql.com/doc/refman/8.0/en/load-data.html 

And We can fix this issue by assigning the column 'description' to a variable, and then set it to the UNHEXed version of that variable:

LOAD DATA INFILE 'MyFile.csv' INTO TABLE `MyTable` Fields Terminated by ',' Lines Terminated by '\n' (pid, count, @var1) SET description = UNHEX(@var1);

2- Or another easier method is once data imported, we can execute update statement on that column:

update MyTable set descritpion = UNHEX(description);

• DateTime data either emptry string or null will be imported as ‘0000-00-00 00:00:00.000’ instead of default value null set in the table schema

The issue with this is because mysql set sql_mode to NO_ZERO_DATE.

To fix this issue, we can set global sql_mode to ALLOW_INVALID_DATES by executing a query below:

SET GLOBAL SQL_MODE='ALLOW_INVALID_DATES';
update MyTable set MyColumnDateTime = null where MyColumnDateTime  = '0000-00-00 00:00:00.000';

Be aware:
Sometimes with Load Data Infile, Uniqueidentify string aka guid e.g '2D3299C9-2CEA-48BE-BF4F-1115DA0CDCC5' will be imported in mysql varchar(64) column as small letter case '2d3299c9-2cea-48be-bf4f-1115da0cdcc5'

Be aware:                
Load Data Infile vs Load Data Local Infile

LOAD DATA INFILE gets the file from the database server's local filesystem. The file has to be located in the database directory or have world read permissions, and the client username must have the FILE privilege.

LOAD DATA LOCAL INFILE reads the file on the client, and sends the contents to the server.
When using Load Data Local Infile you will have to specify option '--local-infile=1' in the mysql commandline:
e.g: 
mysql --local-infile=1 -u MyUser -PMyPwd MyDB -e " Load Data Local Infile 'MyFile.csv' Into Table   'MyTable' Fields Terminated by ',' Lines Terminated by '\n'; "

Be aware: 
Bit column field
Load Data  infile from text into Bit type column: 
non empty string will be consider as bit 1 so even '0' from witin text file will be inserted into mysql by Load Data Infile as Bit'1 than what we expected to be Bit'0 but instead it inserted it as Bit'1. 
So in short:
For the bit field, the value from the text file is interpreted as "whether there is a value then it inserted as Bit'1 or there isn't any value (empty string), it inserted as Bit'0". Any value such as 0,1,2134,'True', 'foo', 'bla' is interpreted as Bit'1 and this is a surpise finding for me as I am currently working on migrating data from mssql to mysql. 
So to import Bit'0 from text file we need to set it to empty or none instead of e.g ,0

FYI: 
Field column with file path
For instance, we are importing an exported data .csv or .dat file from mssql server which one of the tables has a field named MyFilePathNameColumn and it stores a file's full path as c:\myfolder\myfile.txt in the .dat file so when we are importing this .dat file data to mysql using Load Data Infile method, we would expect it to be inserted as c:\myfolder\myfile.txt but instead it inserted it as c:myfoldermyfile.txt because mysql uses Linux's grammar in path convention so it uses forward slash in the file path like Linux hence back slash is removed.
To resolve this issue, when doing the query out of mssql data using bcp.exe we can use mssql's built in function REPLACE to replace '\' with '/' as seen:

select REPLACE(MyFilePathNameColumn, '\', '/')" from MyTable;