TiDB and MySQL: Default Differences



In the previous video, I talked about TiDB's behavior differences from MySQL. As a follow-on, I'm now going to cover differences in defaults between TiDB and MySQL 5.7. This is really an extension of behavior differences, since TiDB is behaving in a way that MySQL does not out of the box, but with a configuration change (either to TiDB or MySQL) the two systems could be made to behave the same.

Character set and collation

TiDB only supports UTF-8 (which behaves like utf8mb4 in MySQL parlance), and binary collations.

This means that if you run a query like select * from users where name = ‘xyz’, the xyz part is case sensitive and accent sensitive. The ordering of the characters will also be based on the byte order, which may be different from the collation order in MySQL.

MySQL 5.7 by contrast defaults to latin1 and case insensitive for comparisons. This changes to utf8mb4 in MySQL 8.0, but still insensitive.

SQL mode

Importantly, TiDB supports the ability to run MySQL in both strict and unstrict SQL mode, giving your applications an upgrade path from earlier versions of MySQL. Like MySQL 5.7, it defaults to strict mode enabled.

TiDB does not yet support all of MySQL's SQL modes. The defaults between MySQL and TiDB's SQL mode differ slightly, but not enough to affect most applications.

Lower case table name sensitivity

In TiDB, names of all tables are lower case, and this change is not directly configurable. In MySQL parlance, this is the same as setting lower_case_table_names=2.

And that's it. We've covered all the major differences between TiDB and MySQL.

In the lab for this section, we'll be loading sample data into TiDB and exploring it in a little bit of detail. If you have MySQL installed somewhere and want to try the same steps, feel free to do so!

The commands are, of course, identical.