There is a way to ““deaccent” the string being compared” without the use of a substitution function that lists all the accents you want to remove… Here is the easiest solution I can think about to remove accents (and other diacritics) from a string. See it in action: var string = ‘Ça été Mičić. ÀÉÏÓÛ’; … Read more
Here’s how to check which columns are the wrong collation: SELECT table_schema, table_name, column_name, character_set_name, collation_name FROM information_schema.columns WHERE collation_name=”latin1_general_ci” ORDER BY table_schema, table_name,ordinal_position; And here’s the query to fix it: ALTER TABLE tbl_name CONVERT TO CHARACTER SET latin1 COLLATE ‘latin1_swedish_ci’; Link
Can be done in a single command (rather than 148 of PHP): mysql –database=dbname -B -N -e “SHOW TABLES” \ | awk ‘{print “SET foreign_key_checks = 0; ALTER TABLE”, $1, “CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 1; “}’ \ | mysql –database=dbname & You’ve got to love the commandline… (You might … Read more
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 … Read more
You can resolve the issue by forcing the collation used in a query to be a particular collation, e.g. SQL_Latin1_General_CP1_CI_AS or DATABASE_DEFAULT. For example: SELECT MyColumn FROM FirstTable a INNER JOIN SecondTable b ON a.MyID COLLATE SQL_Latin1_General_CP1_CI_AS = b.YourID COLLATE SQL_Latin1_General_CP1_CI_AS In the above query, a.MyID and b.YourID would be columns with a text-based data … Read more
I am guessing you have different collations on the tables you are joining. It says you are using an illegal mix of collations in operations =. So you need to set collation. For example: WHERE tableA.field COLLATE utf8mb4_general_ci = tableB.field Then you have set the same collations on the = operation. Since you have not … Read more
You can use the collate clause in a query (I can’t find my example right now, so my syntax is probably wrong – I hope it points you in the right direction) select sone_field collate SQL_Latin1_General_CP850_CI_AI from table_1 inner join table_2 on (table_1.field collate SQL_Latin1_General_CP850_CI_AI = table_2.field) where whatever
Encodings In most cases, SQL Server stores Unicode data (i.e. that which is found in the XML and N-prefixed types) in UCS-2 / UTF-16 (storage is the same, UTF-16 merely handles Supplementary Characters correctly). This is not configurable: there is no option to use either UTF-8 or UTF-32 (see UPDATE section at the bottom re: … Read more
You could use SHOW FULL COLUMNS FROM tablename which returns a column Collation, for example for a table ‘accounts’ with a special collation on the column ‘name’ mysql> SHOW FULL COLUMNS FROM accounts; +———-+————–+——————-+——+—–+———+———-+ | Field | Type | Collation | Null | Key | Default | Extra | +———-+————–+——————-+——+—–+———+———-| | id | int(11) | … Read more
On Unix, table names are case sensitive. On Windows, they are not. Fun, isn’t it? Kinda like their respective file systems. Do you think it’s a coincidence? In other words, if you are developing on Windows but planning on deploying to a Linux machine, better test your SQL against a Linux-based MySQL too, or be … Read more