DEV Community

yoshidaagri
yoshidaagri

Posted on

1 2

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 );

API Trace View

How I Cut 22.3 Seconds Off an API Call with Sentry πŸ‘€

Struggling with slow API calls? Dan Mindru walks through how he used Sentry's new Trace View feature to shave off 22.3 seconds from an API call.

Get a practical walkthrough of how to identify bottlenecks, split tasks into multiple parallel tasks, identify slow AI model calls, and more.

Read more β†’

Top comments (0)

Speedy emails, satisfied customers

Postmark Image

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.

Sign up

πŸ‘‹ Kindness is contagious

Please leave a ❀️ or a friendly comment on this post if you found it helpful!

Okay