Working with Database Structures - Learning MySQL - page 197

mysql> SELECT * FROM show_padding;
+----------+
| mystring |
+----------+
| a |
| abc |
| abcde |
| abcdefg |
+----------+
4 rows in set (0.01 sec)
As you can see, the trailing spaces aren't shown in the last row. They're also ignored
if you try to find strings that have a trailing space:
mysql> SELECT * FROM show_padding WHERE mystring LIKE '% ';
Empty set (0.00 sec)
Since trailing spaces are ignored, no matches are reported.
Note that this has an interesting side effect: you can't differentiate between strings
of spaces alone; the strings " " and " " are considered to be the same thing.
Consequently, you can't use one value in the primary key if you've already got the
other. Consider an example; we can create a table to store names and email ad-
dresses, with the email address as the primary key:
mysql> CREATE TABLE contacts (name CHAR(40), email CHAR(40) PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO contacts VALUES('Sarah', '[e-mail]');
Query OK, 1 row affected (0.01 sec)
So far, so good. Now, if we don't know someone's email address, we can store an
empty string:
mysql> INSERT INTO contacts VALUES('Zahra', '');
Query OK, 1 row affected (0.00 sec)
Note that an empty string is not NULL , so MySQL doesn't complain; however, since
the email address is the primary key, we can't store another empty string. Let's try
storing a single space:
mysql> INSERT INTO Contacts VALUES('Samaneh', ' ');
ERROR 1062 (23000): Duplicate entry '' for key 1
MySQL complains about a duplicate key, since the single space is treated as an
empty string. Trying to insert the string "not sure" works, but then
"not sure " (with a trailing space) doesn't work:
mysql> INSERT INTO Contacts VALUES('Samaneh', 'not sure');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO Contacts VALUES('Sadri', 'not sure ');
ERROR 1062 (23000): Duplicate entry 'not sure' for key 1
Leading spaces don't cause any problems: