Working with Database Structures - Learning MySQL - page 196

mysql> CREATE TABLE bool(id INT, bit CHAR(0) 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,''), (2,NULL), (3,'1');
Query OK, 3 rows affected, 1 warning (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 1
These all look the same:
mysql> SELECT * FROM bool;
+----+------+
| id | bit |
+----+------+
| 1 | |
| 2 | |
| 3 | |
+----+------+
3 rows in set (0.00 sec)
However, one is NULL :
mysql> SELECT * FROM bool WHERE bit IS NULL;
+----+------+
| id | bit |
+----+------+
| 2 | |
+----+------+
1 row in set (0.00 sec)
and the other two aren't:
mysql> SELECT * FROM bool WHERE bit IS NOT NULL;
+----+------+
| id | bit |
+----+------+
| 1 | |
| 3 | |
+----+------+
2 rows in set (0.01 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