Learning Mysql

mysql> CREATE TABLE bool(id INT, bit CHAR) NULL);
Query OK, 0 rows affected (0.02 sec)
Now, let’s add three values an empty string ''
, NULL, and the character
1
:
mysql> INSERT INTO bool VALUES (1,''), (NULL, (3,'1');
Query OK, 3 rows affected, 1 warning (0.01 sec)
Records: 3 Duplicates 0 Warnings These all look the same:
mysql> SELECT * FROM bool;
+----+------+
| id | bit |
+----+------+
| 1 | |
| 2 | |
| 3 | |
+----+------+
3 rows inset sec)
However, one is
NULL
:
mysql> SELECT * FROM bool WHERE bit IS NULL;
+----+------+
| id | bit |
+----+------+
| 2 | |
+----+------+
1 row inset sec)
and the other two aren’t:
mysql> SELECT * FROM bool WHERE bit IS NOT NULL;
+----+------+
| id | bit |
+----+------+
| 1 | |
| 3 | |
+----+------+
2 rows inset sec)
In all other cases, the
CHAR
type takes exactly the number of bytes in storage space as the width of the column (assuming your chosen character set uses one byte per character. Values that are less than
width
characters in length are stored left- aligned in the allocated space, with space character padding on the right side. All trailing spaces are ignored when retrieving and displaying values, as in this example:
mysql> CREATE TABLE show_padding(mystring CHAR(10));
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO show_padding VALUES ('a'),('abc'),('abcde'),('abcdefg ');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates 0 Warnings 0
196 | Chapter 6:
Working with Database Structures

mysql> SELECT * FROM show_padding;
+----------+
| mystring |
+----------+
| a |
| abc |
| abcde |
| abcdefg |
+----------+
4 rows inset sec)
As you can seethe 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 addresses, with the email address as the primary key:
mysql> CREATE TABLE contacts (name CHAR, email CHAR) PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO contacts VALUES('Sarah', 'sarah@learningmysql.com');
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 Leading spaces don’t cause any problems:

Download 4.24 Mb.

Share with your friends: