DEV Community

yoshidaagri
yoshidaagri

Posted on

Problem when line feed code is mixed when csv in import into varchar column with AWS Aurora.

[Environment]

  • AWS Aurora or Mysql

[Problem]

  • When csv file is imported to string column,If the criteria are enclosed in single quotes, will not produce the desired result.
where target_column = '31000000000';
  • If the criteria are not enclosed in single quotes,ok.
where target_column = 31000000000;

[Cause]

  • If you import a csv file without double quotes in the loader,Imports up to unnecessary line feed codes.
  • Line feed code is usually invisible in database.It is necessary to look in hexadecimal.
value memo hex(value)
31000000000 csv 33313030303030303030300D
31000000000 DML 3331303030303030303030

ā€™0Dā€™ is Line feed code of UTF-8.

[point]rule of mysql.

  • For conditional expression [string = number],Compare strings on left side as decimal numbers.line feed code is invalid.

  • For conditional expression [string = string],The character strings on the left side are compared as they are.line feed code is valid.

[How to]

  • To see the line feed code, convert it to hexadecimal.my case is LF.In hexadecimal, it is ā€œ0dā€.
  • This DML deletes only the line feed code.
update target_table_name set target_column_name = TRIM( UNHEX('0D') FROM target_column_name );

Top comments (0)