Trailing spaces of char in MySQL

Answer:

MySQL collations are of type PADSPACE. This means that all CHAR and VARCHAR values in MySQL are compared without regard to any trailing spaces.

E.g.

1. Create a table

mysql> CREATE TABLE t1 ( 
    c CHAR(10), 
    v VARCHAR(10) 
) ENGINE = INNODB;

2. Insert some data

mysql> INSERT INTO t1 VALUES ('foo', 'bar');

3. Select the data back

mysql> SELECT COUNT(*) FROM t1 WHERE c = 'foo';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM t1 WHERE c = 'foo   ';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

As you can see, the trailing spaces makes no difference.

Reference: http://dev.mysql.com/doc/refman/5.1/en/char.html

Vertical (\G) output of query result in MySQL client

Answer:

Sometimes, a query returning too much columns will make the query output not very readable in the MySQL client.

You can use the \G, instead of ; when executing the query, so the vertical output is shown.

mysql> show variables like '%thread%' \G
*************************** 1. row ***************************
Variable_name: max_delayed_threads
        Value: 20
*************************** 2. row ***************************
Variable_name: max_insert_delayed_threads
        Value: 20
*************************** 3. row ***************************
Variable_name: myisam_repair_threads
        Value: 1
*************************** 4. row ***************************
Variable_name: pseudo_thread_id
        Value: 169
*************************** 5. row ***************************
Variable_name: thread_cache_size
        Value: 12
*************************** 6. row ***************************
Variable_name: thread_handling
        Value: one-thread-per-connection
*************************** 7. row ***************************
Variable_name: thread_stack
        Value: 262144
7 rows in set (0.00 sec)

Repair table in MySQL

Answer:

If you are using the MyISAM storage engine, database might be corrupted when the computer was shutdown unexpectedly. To repair the table, you can use the following command in the MySQL client.

mysql> REPAIR TABLE my_table;