将MySQL字符串数据清除为纯文本并修剪开始/结束空格的最佳方法? [英] Best way to clean MySQL string data to plaintext and trim beginning/end spaces?

问题描述

我已经有从网络表中抓取的数据，并且我注意到某些条目中有一些前导空格和nbsp.我意识到我应该在将数据插入MySQL之前先对其进行清理，但是那是一段时间前的事了，如果不需要的话，我不想重复该过程.昨晚我想出了这个PHP脚本，直到我尝试更新条目之前，它一直有效.

I already have data I scraped from a web table and I've noticed some leading spaces and nbsp in some entries. I realize I should have cleaned the data while scraping before inserting it into MySQL but it was a while ago and I don't feel like repeating the process if I don't have to. I came up with this PHP script (late) last night and it works up until I try to update the entries.

<?php
require_once("login.php");

$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: " . mysql_error());

mysql_select_db($db_database);

$query = "SELECT * FROM ingredients;";

$result = mysql_query($query);
$i = 1;
$e = array();
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
    //echo $row[1];
    $str = trim(mysql_real_escape_string($row[1]));
    $e[] = $str;
    $i++;

}

//print_r($e);
/*
$i = 1;
foreach($e as $entry) {
    $query = "UPDATE ingredients
        SET ing_name = $entry
        WHERE ing_id = $i;";
    mysql_query($query);
    $i++;
}*/
?>

问题对:

1. 有没有一种方法可以在MySQL中严格地执行此操作而不使用PHP?
2. 我应该使用什么功能剥离字符串以转换为纯文本并删除所有前导，尾随和nbsp空格?
3. 在更新数据之前，PHP脚本似乎可以工作，这有什么用?

谢谢

推荐答案

有大量的

There's a large number of MySQL String Functions that can help with this sort of thing.

您可能需要组合使用REPLACE和TRIM进行清理:

You might need a combination of REPLACE and TRIM to clean it up:

UPDATE table SET column=TRIM(REPLACE(column, ' ', ' '))

也请尝试不使用mysql_query，而是使用

Also try not to use mysql_query, but use PDO instead. Writing queries directly leaves you exposed to SQL injection problems.

这篇关于将MySQL字符串数据清除为纯文本并修剪开始/结束空格的最佳方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！