Mysql:如何在我的LOAD DATA INFILE查询中使用RTRIM? [英] Mysql: How can I use RTRIM in my LOAD DATA INFILE query?

问题描述

在我的代码中，我有一个如下查询:

In my code I have a query that looks like this:

$load_query = "LOAD DATA LOCAL INFILE '{$file}' INTO TABLE `{$table}`
      FIELDS TERMINATED BY ',' ENCLOSED BY '\"';";

这是我要加载的文件中包含的示例行:

Here is an example row included in the file that I am trying to load:

"MC318199","06160","1","P","00750","00000","TN598792","04/16/2009","91X"                 

您会注意到，在示例行的末尾有很多空格.这些空格都出现在终止行的换行符"\ n"之前.问题是这些空格被输入到数据库中.

You will notice that at the end of the example row there are quite a few spaces. These spaces all occur before the new line character "\n" that terminates the line. The problem is that these spaces get entered into the database.

在运行LOAD DATA INFILE命令时如何删除其他空格?有没有办法使用RTRIM?

How can I remove the additional spaces when running the LOAD DATA INFILE command? Is there a way to use RTRIM?

修改

按照 ajreal 的建议，我必须重新准备文件.准备文件后，将其正确插入数据库.我修改了位于以下位置的bash脚本: http://gabeanderson.com/2008/02/01/unixlinux-find-replace-in-multiple-files/即可完成此操作.代码如下所示:

As ajreal suggested I had to re-prepare the file. After the file was prepared it was inserted into the database correctly. I modified the bash script found at: http://gabeanderson.com/2008/02/01/unixlinux-find-replace-in-multiple-files/ to accomplish this. The code is shown below:

#!/bin/bash 
for fl in *.txt; do
  mv $fl $fl.old
  sed 's/[ \t]*$//' $fl.old > $fl
  rm -f $fl.old
done

推荐答案

邓恩认为太复杂了，就在您加载数据后
update $table set $last_column=rtrim($last_column);

dun think too complicate, right after u loaded the data
update $table set $last_column=rtrim($last_column);

或使用vi(或任何编辑器)手动删除$ file中的空格

or manually remove the space in the $file using vi (or any editor)

或重新准备$ file

or re-preapare the $file

这篇关于Mysql:如何在我的LOAD DATA INFILE查询中使用RTRIM?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！