What effects does using a binary collation have?

by

Binary collation compares your string exactly as strcmp() in C would do, if characters are different (be it just case or diacritics difference). The downside of it that the sort order is not natural.

An example of unnatural sort order (as in “binary” is) : A,B,a,b
Natural sort order would be in this case e.g : A,a,B,b (small and capital variations of the same letter are sorted next to each other)

The practical advantage of binary collation is its speed, as string comparison is very simple/fast. In general case, indexes with binary might not produce expected results for sort, however for exact matches they can be useful.

More Related Contents:

  1. What’s the difference between utf8_general_ci and utf8_unicode_ci?
  2. Authentication plugin ‘caching_sha2_password’ cannot be loaded
  3. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  4. Is there a MySQL option/feature to track history of changes to records?
  5. Binary Data in MySQL [closed]
  6. Can a table field contain a hyphen?
  7. Auto Generate Database Diagram MySQL [closed]
  8. How can I modify the size of column in a MySQL table?
  9. View’s SELECT contains a subquery in the FROM clause
  10. MySQL : Multiple row as comma separated single row
  11. How to re-sync the Mysql DB if Master and slave have different database incase of Mysql replication?
  12. Case statement in MySQL
  13. SQL Order By Count
  14. Export a MySQL Database to SQLite Database
  15. What resources exist for Database performance-tuning? [closed]
  16. How to connect to MySQL from the command line
  17. How to find out the MySQL root password
  18. Can’t connect to MySQL server on ‘127.0.0.1’ (10061) (2003)
  19. How to display open transactions in MySQL
  20. How to produce “phantom read” in REPEATABLE READ? (MySQL)
  21. MySQL query, MAX() + GROUP BY
  22. How to schedule a stored procedure in MySQL
  23. Easiest way to copy a table from one database to another?
  24. What are the differences between the BLOB and TEXT datatypes in MySQL?
  25. Native JSON support in MYSQL 5.7 : what are the pros and cons of JSON data type in MYSQL?
  26. Foreign key constraints: When to use ON UPDATE and ON DELETE
  27. remove all numeric characters from column mysql
  28. Selecting distinct 2 columns combination in mysql
  29. Inventory management with stock options
  30. Failed to connect to mysql at 127.0.0.1:3306 with user root access denied for user ‘root’@’localhost'(using password:YES)

Leave a Comment