Read more

Show the character set and the collation of your MySQL tables

Kim Klotz
October 24, 2011Software engineer at makandra GmbH

To show the collation of your tables you have to login to the MySQL console and execute SHOW TABLE STATUS FROM database;

mysql> SHOW TABLE STATUS FROM test;
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name        | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| test1       | MyISAM |      10 | Dynamic    |    3 |            121 |         364 | 281474976710655 |         2048 |         0 |              4 | 2011-06-13 22:09:36 | 2011-06-13 22:09:36 | NULL       | utf8_general_ci |     NULL |                |         |
| attachments | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |   3145728 |              1 | 2011-08-17 13:46:48 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
2 rows in set (0.05 sec)
Illustration online protection

Rails Long Term Support

Rails LTS provides security patches for old versions of Ruby on Rails (2.3, 3.2, 4.2 and 5.2)

  • Prevents you from data breaches and liability risks
  • Upgrade at your own pace
  • Works with modern Rubies
Read more Show archive.org snapshot

To show the default character set of a table you have to login to the MySQL console and execute SHOW CREATE TABLE table;

mysql> SHOW CREATE TABLE foobar;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                     |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| answers | CREATE TABLE `foobar` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Please note that this is just the default character set of this table. Each column can have a different character set.

Posted by Kim Klotz to makandra dev (2011-10-24 17:42)