Is there a `SQL` or `PHP` script that I can run that will change the default collation in all tables and fields in a database?

I can write one myself, but I think that this should be something that readily available at a site like this. If I can come up with one myself before somebody posts one, I will post it myself.

A script to change all tables and fields to the utf-8-bin collation in MYSQL

I am working on a view, wherein I am using an inner join on two tables which are from two different servers. We are using linked server. When running the query I am getting this message:

&gt;    Cannot resolve the collation conflict between &quot;SQL_Latin1_General_CP1_CI_AS&quot; and &quot;Arabic_CI_AS&quot; in the equal to operation.

I don&#39;t know much about collation. Searching through internet I find solutions to use `COLLATE`, but the concept of `COLLATE` is not clear to me. Will it change anything for any of the databases? I am looking for a solution without changing anything for the databases.

Any good learning material for these concepts is welcome.

How to fix a collation conflict in a SQL Server query?

Just got this answer from a previous question and it works a treat!

    SELECT username, (SUM(rating)/COUNT(*)) as TheAverage, Count(*) as TheCount 
    FROM ratings WHERE month=&#39;Aug&#39; GROUP BY username HAVING TheCount &gt; 4
    ORDER BY TheAverage DESC, TheCount DESC

But when I stick this extra bit in it gives this error:

&gt; Documentation #1267 - Illegal mix of
&gt; collations
&gt; (latin1_swedish_ci,IMPLICIT) and
&gt; (latin1_general_ci,IMPLICIT) for
&gt; operation &#39;=&#39;

    SELECT username, (SUM(rating)/COUNT(*)) as TheAverage, Count(*) as TheCount FROM 
    ratings WHERE month=&#39;Aug&#39; 
    **AND username IN (SELECT username FROM users WHERE gender =1)**
    GROUP BY username HAVING TheCount &gt; 4 ORDER BY TheAverage DESC, TheCount DESC

The table is:

`id, username, rating, month`

Illegal mix of collations error in MySql

Is there a query that can be run in mysql that shows all tables and their default collation?  Even better if there was on that could show all collations on all columns of all tables.

Query to show all tables and their collation

Between `utf8_general_ci` and `utf8_unicode_ci`, are there any differences in terms of performance?

What&#39;s the difference between utf8_general_ci and utf8_unicode_ci?

I have moved one of our databases (DB1) from SQL Server 2008 to 2012 and when I run the stored procedures I get the following error

&gt; Cannot resolve the collation conflict between &quot;SQL_Latin1_General_CP1_CI_AS&quot; and &quot;Latin1_General_CI_AS&quot; in the equal to operation

I changed the collation on the database using 

    ALTER DATABASE [optimiser] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    ALTER DATABASE [optimiser] COLLATE SQL_Latin1_General_CP1_CI_AS
    ALTER DATABASE [optimiser] SET MULTI_USER

But I still get the error whenever the stored procedures run. I believe because the SP is using a join to another database (GE&#39;s ihistorian) and it has a collation mismatch. IS there anyway to resolve this.

On the old server DB1 was set as `Latin1_General_CI_AS` and this works fine. The new location for the DB has a default of `SQL_Latin1_General_CP1_CI_AS`. Is it worth changing the collation n DB1 on the new server back to `Latin1_General_CI_AS`??

Cannot Resolve Collation Conflict

I&#39;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. However, I can&#39;t find a clear a distinction between UTF-8 General CI and UTF-8 Unicode CI.

1. Should I be storing user-submitted content in UTF-8 General or UTF-8 Unicode CI columns?
2. What type of data would UTF-8 Binary be applicable to?

UTF-8: General? Bin? Unicode?

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?

How to change collation of database, table, column?

Am getting the below error when trying to do a select through a stored procedure in MySQL.

&gt; Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation &#39;=&#39;

Any idea on what might be going wrong here?

The collation of the table is `latin1_general_ci` and that of the column in the where clause is `latin1_general_cs`.

Troubleshooting &quot;Illegal mix of collations&quot; error in mysql

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’

Any help would be appreciated!

#1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’

    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 &#39;latin1_general_ci&#39;.

Then what is the effect of the alter table command?

How to change the default collation of a table?

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 collation and get back the detail of character?

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

I&#39;m wondering if there is a &quot;best&quot; choice for collation in MySQL for a general website where you aren&#39;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 PHP.

In the past I have set PHP to output in &quot;UTF-8&quot;, but which collation does this match in MySQL? I&#39;m thinking it&#39;s one of the UTF-8 ones, but I have used `utf8_unicode_ci`, `utf8_general_ci`, and `utf8_bin` before.

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

What is the (default) charset for:

- MySQL database

- MySQL table

- MySQL column



How do I see what character set a MySQL database / table / column is?

I have an SQL query to create the database in SQLServer as given below:

&lt;!-- language: lang-sql --&gt;

    create database yourdb
    on
    ( name = &#39;yourdb_dat&#39;,
      filename = &#39;c:\program files\microsoft sql server\mssql.1\mssql\data\yourdbdat.mdf&#39;,
      size = 25mb,
      maxsize = 1500mb,
      filegrowth = 10mb )
    log on
    ( name = &#39;yourdb_log&#39;,
      filename = &#39;c:\program files\microsoft sql server\mssql.1\mssql\data\yourdblog.ldf&#39;,
      size = 7mb,
      maxsize = 375mb,
      filegrowth = 10mb )
    COLLATE SQL_Latin1_General_CP1_CI_AS;
    go

It runs fine.

While rest of the SQL is clear to be I am quite confused about the functionality of `COLLATE SQL_Latin1_General_CP1_CI_AS`.

Can anyone explain this to me? Also, I would like to know if creating the database in this way is a best practice?

What does &#39;COLLATE SQL_Latin1_General_CP1_CI_AS&#39; do?

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`?


Why is MySQL&#39;s default collation latin1_swedish_ci?

How can I see what collation a table has? I.E. I want to see:

    +-----------------------------+
    |  table  |     collation     |
    |-----------------------------|
    | t_name  |  latin_general_ci |
    +-----------------------------+


mysql check collation of a table

For a poor man&#39;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 languages sort differently):

&lt;pre&gt;
native sorting gets it wrong: a b c o u z &#228; &#246; &#252;
collation-correct would be:   a &#228; b c o &#246; u &#252; z
&lt;/pre&gt;

Basically, I need all occurrences of &quot;&#228;&quot; of a given string replaced with &quot;a&quot; (and so on). This way the result of native sorting would be very close to what a user would expect (or what a database would return). 

Other languages have facilities to do just that: [Python supplies `str.translate()`][1], in [Perl there is `tr/…/…/`][2], [XPath has a function `translate()`][3], [ColdFusion has `ReplaceList()`][4]. But what about JavaScript?

Here is what I have right now.

    // s would be a rather short string (something like 
    // 200 characters at max, most of the time much less)
    function makeSortString(s) {
      var translate = {
        &quot;&#228;&quot;: &quot;a&quot;, &quot;&#246;&quot;: &quot;o&quot;, &quot;&#252;&quot;: &quot;u&quot;,
        &quot;&#196;&quot;: &quot;A&quot;, &quot;&#214;&quot;: &quot;O&quot;, &quot;&#220;&quot;: &quot;U&quot;   // probably more to come
      };
      var translate_re = /[&#246;&#228;&#252;&#214;&#196;&#220;]/g;
      return ( s.replace(translate_re, function(match) { 
        return translate[match]; 
      }) );
    }

For starters, I don&#39;t like the fact that the regex is rebuilt every time I call the function. I guess a closure can help in this regard, but I don&#39;t seem to get the hang of it for some reason.

Can someone think of something more efficient?

****

## Answers below fall in two categories: ##

1. String replacement functions of varying degrees of completeness and efficiency (what I was originally asking about)
2. A [late mention](https://stackoverflow.com/a/42163018/18771) of  [`String#localeCompare`][5], which is now [widely supported][6] among JS engines (not so much at the time of the question) and could solve this category of problem much more elegantly.


  [1]: http://docs.python.org/3.1/library/stdtypes.html#str.translate
  [2]: http://perldoc.perl.org/functions/tr.html
  [3]: http://www.w3.org/TR/xpath/#function-translate
  [4]: http://livedocs.adobe.com/coldfusion/8/functions_m-r_33.html
  [5]: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String/localeCompare
  [6]: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String/localeCompare#Browser_compatibility

Efficiently replace all accented characters in a string?

Python sorts by byte value by default, which means &#233; 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&#39;t find anything. Preferrably sorting should have language support so it understands that &#229;&#228;&#246; should be sorted after z in Swedish, but that &#252; should be sorted by u, etc. Unicode support is thereby pretty much a requirement.

If there is no library for it, what is the best way to do this? Just make a mapping from letter to a integer value and map the string to a integer list with that?


How do I sort unicode strings alphabetically in Python?

Is there a rule when we must use the Unicode types?  
 
I have seen that most of the European languages (German, Italian, English, ...) are fine in the same database in VARCHAR columns.

I am looking for something like: 

 1. If you have Chinese --&gt; use NVARCHAR
 2. If you have German and Arabic --&gt; use NVARCHAR

What about the collation of the server/database?

I don&#39;t want to use always NVARCHAR like suggested here
[https://stackoverflow.com/questions/35366/varchar-vs-nvarchar-performance][1]


  [1]: https://stackoverflow.com/questions/35366/varchar-vs-nvarchar-performance

When must we use NVARCHAR/NCHAR instead of VARCHAR/CHAR in SQL Server?

If I have a column names called `category_id` and `Category_Id`, are they different?

And if I have table called `category` and `Category`, are they different?

 

Are column and table name case sensitive in MySQL?

I&#39;m looking to compare two varchars in SQL, one would be something like `Cafe` and the other `Caf&#233;` is there a way in SQL that will allow the two values to be compared. For instance:

    SELECT *
    FROM Venue
    WHERE Name Like &#39;%cafe%&#39;

So if there is a venue with the name `Big Bobs Caf&#233; Extraordinaire` it would be included in the result set?

How do I perform an accent insensitive compare (e with &#232;, &#233;, &#234; and &#235;) in SQL Server?

By default - what is the character encoding set for a database in Microsoft SQL Server?

How can I see the current character encoding in SQL Server?

SQL Server default character encoding

I previously created a MySQL table and now I want to find out what collation some of the fields are using. What SQL or MySQL commands can I use to discover this?



Discover collation of a MySQL column

I know, I know with what I wrote in the question I shouldn&#39;t be surprised.  But my situation is slowly working on an inherited POS system and my predecessor apparently wasn&#39;t aware of JOINs so when I looked into one of the internal pages that loads for 60 seconds I see that it&#39;s a fairly quick, rewrite these 8 queries as one query with JOINs situation.  Problem is that besides not knowing about JOINs he also seems to have had a fetish for multiple databases and surprise, surprise they use different collations.  Fact of the matter is we use all &quot;normal&quot; latin characters that English speaking people would consider the entire alphabet and this whole thing will be out of use in a few months so a bandaid is all I need.

Long story short is I need some kind of method to cast to a single collation so I can compare two fields from two databases.

Exact error is:

&gt; Cannot resolve the collation conflict
&gt; between
&gt; &quot;SQL_Latin1_General_CP850_CI_AI&quot; and
&gt; &quot;SQL_Latin1_General_CP1_CI_AS&quot; in the
&gt; equal to operation.

Doing a join across two databases with different collations on SQL Server and getting an error

While answering [this question][1], I became uncertain about something that I didn&#39;t manage to find a sufficient answer to.

What are the practical differences between using the binary `utf8_bin` and the case insensitive `utf8_general_ci` collations? 

I can see three:

 1. Both have a different sorting order; `_bin`&#39;s sorting order is likely to put any umlauts to the end of the alphabet, because byte values are compared (right?)

 2. Only case sensitive searches in `_bin` 

 3. No `A = &#196;` equality in `_bin`

Are there any other differences or side-effects to be aware of?




Reference:

 * [9.1.2. Character Sets and Collations in MySQL][2]
 * [9.1.7.6. The _bin and binary Collations][3] in the mySQL manual
 * [9.1.7.7. The BINARY Operator][4]

Similar questions that don&#39;t address the issue:

 * https://stackoverflow.com/questions/2344118/utf-8-general-bin-unicode 


  [1]: https://stackoverflow.com/questions/5526169/what-is-the-best-mysql-collation-for-german-language/5526189#5526189
  [2]: http://dev.mysql.com/doc/refman/5.0/en/charset-mysql.html
  [3]: http://dev.mysql.com/doc/refman/5.0/en/charset-binary-collations.html
  [4]: http://dev.mysql.com/doc/refman/5.0/en/charset-binary-op.html

All Collation Solutions on Gang of Coders

Total of 26 Collation Solutions