Show the character set and the collation of your MySQL tables

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)

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.

Kim Klotz

makandra.com

Say thanks25

Last edit

2012-04-24

License

Source code in this card is licensed under the MIT License.

Show and change MySQL default character set

To show the MySQL default character set you have to login to the MySQL console and execute SHOW VARIABLES LIKE 'char%';

mysql> SHOW VARIABLES LIKE  'char%';
+--------------------------+----------------------------+
| Variable_name  ...

MySQL / MariaDB: Show disk usage of tables and columns

You can find out about disk space usage of all tables within your database by running this:

SELECT table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)` FROM information_schema.TABLES WHERE table_schema = "...

Show a MySQL table's charset, collation and engine

Use this MySQL command to show further info about a table:

SHOW CREATE TABLE tags;

This will output a table schema like this:

CREATE TABLE `tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unico...

CSS: Matching against attributes and their values (or parts of them)

You probably know that you can use CSS selectors to match against elements and their attributes, such as:

a[title] { /* any <a> that has a "title" */ }
a[data-fancy="true"] { /* any <a> that has their "data-fancy" attribute set to "true" *...

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

Show the character set and the collation of your MySQL tables

Related cards:

Introduction

Intro