What is the best MySQL sorting method for cases containing spaces?
P粉793532469
2023-09-02 10:38:35
<p>So, I have a project where the user can update a field and this functionality works fine. </p>
<p>The problem occurs when the user enters a space in the field <code>' '</code>. </p>
<p>For example, suppose the field originally was <code>test1</code> and the user changes it to <code>test 1</code>. The change will take effect and will also print as expected. </p>
<p>However, if the user continues to type anything from <code>test1</code> to <code> test1</code> or anything else, this change will not occur at all. No matter what the user inputs. </p>
<p>I have a feeling this has to do with the collation I'm using in the database, no matter which collation I use, the problem persists, and, frankly, I don't know much about collations. </p>
<p>The code to update the field is very simple: </p>
<pre class="brush:php;toolbar:false;">`$query = $pdo -> prepare("SELECT 1 FROM table WHERE field = ?");
$query -> bindValue(1, $new_name);
$query -> execute();
$num = $query -> rowCount();
if ($num == 0) {
$query = $pdo -> prepare("UPDATE table SET table = ? WHERE table = ?");
$query -> bindValue(1, $new_name);
$query -> bindValue(2, $old_name);
$query -> execute();
}`</pre>
<p>Does anyone have any input on my issue, either on the cause of the problem or how to fix it? </p>
<p>Thank you in advance. </p>
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
To see the exact contents stored in column
col, executeSELECT HEX(col) .... Spaces will appear as 20.To remove leading and trailing spaces entered in
<form>, use PHP'strim()function.Depending on the data type of the column, MySQL will ignore trailing spaces. Are you using
CHAR,VARCHAR,TEXT,BLOB, or another type?Otherwise, leading and internal whitespace will be preserved by all parties involved.
mysql> SELECT HEX("test 1"); +---------------+ | HEX("test 1") | +---------------+ | 746573742031 | +---------------+ t e s t 1