How to discover a collation of a table or database using an SQL query?

Asked

Viewed 4,183 times

5

I can visualize the collation of certain table or bank perfectly by Phpmyadmin.

But if I did not dispose of this aforementioned tool, as I could do to discover the collation by manually doing an SQL query?

mysql collation

2 answers

7

That’s all it takes:

SHOW CREATE TABLE nomedatabela

And from the database:

SHOW CREATE DATABASE Syntax

The way out is something like that:

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `s` char(60) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
                              --^^^^^^-- Aqui é o Charset padrão.

If any column has been set with a charset will also appear in the SHOW CREATE.

More details in the manual:

https://dev.mysql.com/doc/refman/5.7/en/show-create-table.html

6

You can list the collation of each table of a given table using the information_schema, as follows:

SELECT table_name, table_collation FROM information_schema.tables
WHERE table_schema = 'nome_da_base'

If you need to return the collation of each column you can use this query:

SELECT column_name, data_type, collation_name FROM information_schema.columns
WHERE table_name = 'nome_da_tabela'

Browser other questions tagged mysql collation

You are not signed in. Login or sign up in order to post.