Linux Ask! is a Q & A web site specific for Linux related questions. Questions are collected, answered and audited by experienced Linux users.
What are the difference between CHAR BINARY and BINARY type in MySQL
Answer:
For CHAR BINARY, the BINARY keyword does not cause the column to store binary value, instead, it cause the column to use binary collation only. The actual character set used depend on the system.
For BINARY column, the actual data is treated as binary, so no charset and collation rules apply.
Reference: http://dev.mysql.com/doc/refman/5.1/en/binary-varbinary.html
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.
Show which storage engines are available in MySQL
Answer:
To see which storage engines are available in MySQL server, use the following method:
# mysql -e 'show engines' | grep YES
MEMORY YES Hash based, stored in memory, useful for temporary tables
BLACKHOLE YES /dev/null storage engine (anything you write to it disappears)
ARCHIVE YES Archive storage engine
CSV YES CSV storage engine
MRG_MYISAM YES Collection of identical MyISAM tables
MyISAM is not showed as it is the default engine.
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)
Find duplicated records in MySQL
Answer:
Assume you have a table `my_table` which contains a column called "name", how do you find what name has been duplicated in your table?
SELECT name, COUNT(`name`) as cnt
FROM `my_table`
GROUP BY `name`
HAVING cnt > 1
That's all.
