How to See What Character Set a MySQL Database/Table/Column Is

How can I figure out the default charset/collation in my MySQL

Use SHOW CREATE TABLE. For example:

CREATE TABLE a (
    dflt VARCHAR(11),
    cs   VARCHAR(11)  CHARACTER SET latin1,
    cola VARCHAR(11)  COLLATE utf8mb4_hungarian_ci,
    cc   VARCHAR(11)  CHARACTER SET latin1  COLLATE latin1_bin,
    colb VARCHAR(11)  COLLATE latin1_bin
);

SHOW CREATE TABLE a\G

mysql> SHOW CREATE TABLE a\G
*************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `dflt` varchar(11) DEFAULT NULL,
  `cs`   varchar(11) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `cola` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_hungarian_ci DEFAULT NULL,
  `cc`   varchar(11) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `colb` varchar(11) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Notes:

• Most of the columns have the charset and collation spelled out.
• The one that does not (dflt) inherits those settings from the DEFAULTs for the table.
• Each CHARACTER SET has a "default" collation.
• Each COLLATE is associated with exactly one CHARACTER SET, namely the first part of the collation name.
• (Not shown here): The DEFAULTs for the table are inherited from the DATABASE.
• MySQL 8 defaults to CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci if you do nothing.
• Older versions defaulted to CHARSET=latin1 COLLATE=latin1_swedish_ci
• The "0900" or "520" in some collations refers to Unicode Standards versions 9.0 and 5.20. You can infer from this that there may be new, "better", collations in the future.

How to find out the charset of a database and table with PHPmyAdmin?

Try writing a SQL command:

SELECT * FROM information_schema.SCHEMATA S
WHERE schema_name = "myDataBase";

You might want to check this answer and the comments on this ressoure;

How do I search for invisible characters in a database table column?

Remove the visible characters that are normally in usernames from each username, then check which usernames are still not empty but appear empty.

SELECT username, 
       REGEXP_REPLACE(username, "[\\.,{}<>\'a-z0-9@_-]", '') AS second_username 
  FROM mytable 
  HAVING second_username <> '';

`CHARACTER SET=` vs `CONVERT TO CHARACTER SET`

The first example only changes the table's default character set.

This is a metadata-only change, since it doesn't actually change any data, it only changes the table's default.
The default only applies when you add string columns to the table later, without specifying a character set.
Changing the table's default character set does not convert any of the current string columns in the table.
They will remain stored in the former character set.

You can convert columns to the new character set one by one:

ALTER TABLE tbl_name MODIFY COLUMN column1 VARCHAR(50) CHARACTER SET utf8mb4;

Or you can convert all string columns in one alter:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4;

Either of these conversion steps needs to perform a table-copy to rewrite the data. If you have several string columns and you want to convert them all, you might as well use CONVERT TO CHARACTER SET so you only have to do the table-copy once.

What does default do in CHARACTER SET and COLLATE when creating a database?

There's no difference. They do the exact same thing, whether you use the DEFAULT keyword or not. It's just optional syntax. You would use it only for readability or personal preference.

There are other examples of optional syntax in SQL, for example the AS keyword when declaring column aliases or table aliases, or the word COLUMN in ALTER TABLE <name> ADD [COLUMN] <name> ...

Keep in mind SQL was designed by a committee, so there were bound to be some areas where people disagreed on the syntax, and it was easier to make some syntax optional than to get them to agree. :-)

---

Related Topics