Entry

Does MySQL strip whitespace from character fields?

Aug 11th, 2000 13:10
Bruce Christensen, Nathan Wallace, "MySQL & mSQL" by Randy Jay Yarger, et. al, published by O'Reilly p. 97

MySQL strips whitespace from the end of VARCHAR character fields, but 
not TEXT ones.  From the MySQL book p. 97: "In opposition to the ANSI 
standard, VARCHAR in MySQL fields are not padded.  Any extra spaces are 
removed from a value before it is stored."  However, it doesn't strip 
it from the /beginning/ of the fields.
If you need to keep spaces intact, use a TEXT field instead of a 
VARCHAR field.
And here's a little PHP test that you can use to confirm this behavior:
$dbh = mysql_connect("localhost","root","");
mysql_query("USE test");
mysql_query("CREATE TABLE spacetest (vcfield VARCHAR(255), txfield 
TEXT)");
mysql_query("INSERT INTO spacetest SET vcfield=' varchar ', txfield=' 
text '");
$result = mysql_query("SELECT * from spacetest");
$row = mysql_fetch_array($result);
var_dump ($row);
Which produces this output:
array(4) {
  [0]=>
  string(8) " varchar"
  ["vcfield"]=>
  string(8) " varchar"
  [1]=>
  string(6) " text "
  ["txfield"]=>
  string(6) " text "
}