Yes, this is standard behaviour in the non-language-specific unicode collations. 9.1.13.1. Unicode Character Sets To further illustrate, the following equalities hold in both utf8_general_ci and utf8_unicode_ci (for the effect this has in comparisons or when doing searches, see Section 9.1.7.7, “Examples of the Effect of Collation”): Ä = A Ö = O Ü = U … Read more
I believe this error is caused because the local server and live server are running different versions of MySQL. To solve this: Open the sql file in your text editor Find and replace all utf8mb4_unicode_520_ci with utf8mb4_unicode_ci Save and upload to a fresh mySql db Hope that helps
Here are some explanations: Lexicographical In this case, you sort text without considering numbers. In fact, numbers are just “letters”, they have no numeric combined meaning. This means that the text “ABC123” is sorted as the letters A, B, C, 1, 2 and 3, not as A, B, C and then the number 123. This … Read more
This answer is outdated. For full emoji support, see this answer. As the character set, if you can, definitely UTF-8. As the collation – that’s a bit nasty for languages with special characters. There are various types of collations. They can all store all Umlauts and other characters, but they differ in how they treat … 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
You can check the version using SELECT @@VERSION; It it’s 9.00 or greater, you can check the collation of a column using SELECT collation_name FROM sys.columns WHERE name=”column name” AND [object_id] = OBJECT_ID(‘dbo.table name’); And for the database using SELECT collation_name FROM sys.databases WHERE name=”database name”; If it’s < 9.0 then you’re using SQL Server … Read more
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
The real reason you want to use NVARCHAR is when you have different languages in the same column, you need to address the columns in T-SQL without decoding, you want to be able to see the data “natively” in SSMS, or you want to standardize on Unicode. If you treat the database as dumb storage, … 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
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