Questions tagged [collation]

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

Collation is often language, locale, and usage-dependent. Rules can change depending on the language being encoded (e.g., English vs. French), the region of the world (e.g., Canadian French vs. Standard French), and the purpose of the collation (e.g., a dictionary vs. a phone book). Further customisations like case-insensitive sorting are also possible.

Resources

1834 questions
1299
votes
9 answers

What's the difference between utf8_general_ci and utf8_unicode_ci?

Between utf8_general_ci and utf8_unicode_ci, are there any differences in terms of performance?
KahWee Teng
  • 13,658
  • 3
  • 21
  • 21
791
votes
11 answers

What is the best collation to use for MySQL with PHP?

Is there a collation type which is officially recommended by MySQL, for a general website where you aren't 100% sure of what will be entered? I understand that all the encodings should be the same, such as MySQL, Apache, the HTML and anything inside…
Darryl Hein
  • 142,451
  • 95
  • 218
  • 261
786
votes
17 answers

MySQL: Get character-set of database or table or column?

What is the (default) charset for: MySQL database MySQL table MySQL column
Amandasaurus
  • 58,203
  • 71
  • 188
  • 248
308
votes
22 answers

#1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’

I have a WordPress website on my local WAMP server. But when I upload its database to live server, I get error #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’
Shishil Patel
  • 3,449
  • 2
  • 12
  • 16
295
votes
5 answers

UTF-8: General? Bin? Unicode?

I'm trying to figure out what collation I should be using for various types of data. 100% of the content I will be storing is user-submitted. My understanding is that I should be using UTF-8 General CI (Case-Insensitive) instead of UTF-8 Binary.…
Dolph
  • 49,714
  • 13
  • 63
  • 88
280
votes
22 answers

How to change collation of database, table, column?

The database is latin1_general_ci now and I want to change collation to utf8mb4_general_ci. Is there any setting in PhpMyAdmin to change collation of database, table, column? Rather than changing one by one?
user158469
  • 2,803
  • 3
  • 17
  • 4
278
votes
18 answers

Troubleshooting "Illegal mix of collations" error in mysql

Am getting the below error when trying to do a select through a stored procedure in MySQL. Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation '=' Any idea on what might be going wrong here? The…
user355562
  • 3,203
  • 3
  • 21
  • 18
217
votes
4 answers

How to change the default collation of a table?

create table check2(f1 varchar(20),f2 varchar(20)); creates a table with the default collation latin1_general_ci; alter table check2 collate latin1_general_cs; show full columns from check2; shows the individual collation of the columns as…
simplfuzz
  • 12,479
  • 24
  • 84
  • 137
209
votes
5 answers

How to change the CHARACTER SET (and COLLATION) throughout a database?

Our previous programmer set the wrong collation in a table (Mysql). He set it up with Latin collation, when it should be UTF8, and now I have issues. Every record with Chinese and Japan character turn to ??? character. Is possible to change…
Jeg Bagus
  • 4,895
  • 9
  • 43
  • 54
172
votes
5 answers

What does 'COLLATE SQL_Latin1_General_CP1_CI_AS' do?

I have an SQL query to create the database in SQLServer as given below: create database yourdb on ( name = 'yourdb_dat', filename = 'c:\program files\microsoft sql server\mssql.1\mssql\data\yourdbdat.mdf', size = 25mb, maxsize = 1500mb, …
Thunder
  • 10,366
  • 25
  • 84
  • 114
145
votes
5 answers

mysql check collation of a table

How can I see what collation a table has? I.E. I want to see: +-----------------------------+ | table | collation | |-----------------------------| | t_name | latin_general_ci | +-----------------------------+
Scott
  • 3,290
  • 4
  • 29
  • 48
142
votes
5 answers

How to change the default charset of a MySQL table?

There is a MySQL table which has this definition taken from SQLYog Enterprise : Table Create Table ----------------- …
pheromix
  • 18,213
  • 29
  • 88
  • 158
140
votes
2 answers

Why is MySQL's default collation latin1_swedish_ci?

What is the reasoning behind setting latin1_swedish_ci as the compiled default when other options seem much more reasonable, like latin1_general_ci or utf8_general_ci?
Alan
  • 2,897
  • 4
  • 23
  • 27
132
votes
23 answers

Efficiently replace all accented characters in a string?

For a poor man's implementation of near-collation-correct sorting on the client side I need a JavaScript function that does efficient single character replacement in a string. Here is what I mean (note that this applies to German text, other…
Tomalak
  • 332,285
  • 67
  • 532
  • 628
112
votes
11 answers

How do I sort unicode strings alphabetically in Python?

Python sorts by byte value by default, which means é comes after z and other equally funny things. What is the best way to sort alphabetically in Python? Is there a library for this? I couldn't find anything. Preferrably sorting should have language…
Lennart Regebro
  • 167,292
  • 41
  • 224
  • 251
1
2 3
99 100