Update in 2019-10-29
As mentions by @Manuel Jordan in comments, utf8mb4_0900_ai_ci is the new default in MySQL 8.0, so the following is now again a better practice:
CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
Answer before 2019-10-29
Note: The following is now considered a better practice (see bikeman868's answer):
You should use:
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Note that utf8_general_ci is no longer recommended best practice. See the related Q & A:
What's the difference between utf8_general_ci and utf8_unicode_ci on Stack Overflow.
To answer your question: [:ascii:] works. You may have some characters in your text that you do not recognize as non-ASCII, yet they're there. They can be something like a non-breakable space, for instance, or any other Unicode space character.
It is not strange to have non-breakable spaces ( ) in texts that you copy-and-paste from a web page, yet ...
SQL Server does not store UTF-8 under any circumstances. You get either UTF-16 Little Endian (LE) via NVARCHAR (including NCHAR and NTEXT, but don't ever use NTEXT) and XML, or some 8-bit encoding, based on a Code Page, via VARCHAR (including CHAR and TEXT, but don't ever use TEXT).
The problem here is that your code is mistranslating that 0x82 character, ...
Disclosure: I’m the author of How to support full Unicode in MySQL databases, the guide you’re following.
Where did you save the modified settings? Check where mysqld loads the default options from. It’s usually /etc/my.cnf (as mentioned in the guide) but it may be different on your system. Run the following command to find out:
$ mysqld --help --verbose 2&...
There are two possible questions here, and they have two different answers --
How do I make all new tables utf8mb4
It can be done (for one database) while creating a database:
CREATE DATABASE dbname
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;
You can ALTER a database (similar syntax), but that only provides a default ...
UTF-8 support gives you a new set of options. Potential space savings (without row or page compression) is one consideration, but the choice of type and encoding should probably be primarily made on the basis of actual requirements for comparison, sorting, data import, and export.
You may need to change more than you think, since e.g. an nchar(1) type ...
Yes, all _UTF8 collations use code page 65001 as that is the code page for UTF-8. You can even use 65001 in a DOS / Command Window via:
though not all programs and fonts will work seamlessly with it.
For _UTF8 collations, the code page is not controlled by the culture (i.e. Latin1_General vs Arabic) as it is for non-_UTF8 collations because code ...
Your list of characters that must be supported clearly indicates you need nothing more than plain ascii.
If you want to stored this as text, then this ascii is your most compact way. But here are a few clarifications:
VARCHAR(10) does not "need" 80 bits. It may need 80 bits, if all characters are used, under ascii character set. If you only store 3 ...
this can reduce the size of tables and indexes (emphasis added)
Reduction in size is only possible if most of the characters are essentially [space], 0 - 9, A - Z, a - z, and some basic punctuation. Outside of that specific set of characters (in practical usage terms, standard ASCII values 32 - 126), you will be at best equal in size to NVARCHAR / UTF-16, ...
CHAR(...) CHARACTER SET utf8 always takes 3 bytes per character -- CHAR(100) occupies 300 bytes (no length needed).
VARCHAR occupies 1-2 bytes for a length, plus only as many bytes as needed. So VARCHAR(100) with hello will occupy 7 (2+5) bytes in any character set.
Señor, in CHARACTER SET latin1, take 5 bytes (plus length). In utf8, it takes 6 bytes (...
trying to make the database as efficient as possible
There are at least two different types of efficiency that are really at play here:
space (disk and memory)
Under certain conditions (as described in Outman's answer, which is a copy/paste of the "Recommended Uses / Guidance" section of my blog post, linked at the top of that answer) you can save ...
DBMS_LOB package has conversion functions as well. Unfortunately there is no support for converting CLOB to CLOB and change characterset in one step, so the data is first converted to BLOB, then back to CLOB.
CREATE TABLE EXAMPLE (T CLOB);
INSERT INTO EXAMPLE (T) VALUES ('Example Î² ');
for i in 1..12
update example set t = t || t;
The notion that binary collations are the same as case-sensitive collations is, unfortunately, an extremely common one.
However, they are very much not functionally equivalent. There are four areas where behavioral differences can be seen (at least four that I am aware of):
Consider having a lower-case ü ("u" with diaeresis) and an ...
I just solved the charset problem by using Notepad++.
The problem was that my php version is 126.96.36.199 and the hosting firm's was 3.3.8. Because of that, it could not work with the utf8mb4_unicode_520_ci.
To solve the problem open the exported SQL file, search and replace the utf8mb4 with utf8, after that search and replace the utf8mb4_unicode_520_ci with ...
What follows is my speculation.
Java Strings are internally represented using the UTF-16 encoding. When you getBytes("UTF-8") Java converts between the two encodings, and you probably use an up-to-date Java platform.
When you attempt to store a Java String in the database, Oracle also performs conversion between the Java native UTF-16 and the database ...
Here's a list of recommended uses taken from here:
The UTF-8 encoding, being a variable-length encoding, can be a huge benefit in some scenarios, but it can also make things worse in others. Unfortunately, there is very little use for a “_UTF8” encoding given that Data Compression and Clustered Columnstore Indexes are available across all editions of SQL ...
There are three differences as far as I can tell (according to their documentation):
Case-mappings (for LOWER() / UPPER() functions):
The LOWER() and UPPER() functions perform case folding according to the collation of their argument.
The difference between the two ...
Actually, my answer is in the Documentation:
UTF-8 encoding using one to three bytes per character. Basic Latin
letters, numbers and punctuation use one byte. European and Middle
East letters mostly fit into 2 bytes. Korean, Chinese, and Japanese
ideographs use 3-bytes. No supplementary characters are stored.
utf8mb4 -- ...
As you have correctly identified, NadÃ¨ge is the UTF-8 representation of Nadège incorrectly decoded as ISO-8859-1 ("latin-1"). Then, in your case, re-encoded to UTF-8 for storage in the DB.
To fix it you need to:
Take the current representation and decode the UTF-8 to latin-1 as a byte string
re-interpret the byte string, decoding it as utf-8
Case or accent sensitivity behavior is defined by the collation you are using.
The full list of comparable characters in a given collation can be found in collationcharts where you find the MySQL list of collations
If you find your collation in that list you will see a chart of comparable characters.
Balu, the answer you posted will truncate the value to 30 characters because the no length was specified for the CAST and CONVERT declarations of the varchar and nvarchar data types.
Consider using STUFF instead:
SET UnicodeColumn = STUFF(UnicodeColumn, 1, 1, '')
WHERE CAST(LEFT(UnicodeColumn, 1) AS binary(2)) IN(...
The reason your function takes ages is because you have empty values for actual in UTF8Encoding. The patindex expression returns 1 when you check for an empty actual so you never exit the inner loop. You can fix that by adding and actual <> '' to the query against UTF8Encoding. Next issue is where you use @expected as parameter to nchar(). The ...
By using "UTF8 thingies" I think that the problem here would be concepts. Let's review them:
Encoding (badly called character set in MySQL): utf-8 (utf8 and utf8mb4), utf-16 (utf16), 7-bit ASCII (ascii)... Those are the actual formats in which the characters are stored, transmitted or converted. They can use (in MySQL, from 1 byte to 4, and some are dynamic,...
I've also followed that guide, however, the same issue appeared again.
The actual issue lies in command used to create the DB as it takes DEFAULT CHARACTER as an argument and if that is not passed while execution a default character set is passed which is not utfmb4.
Below are the steps that I followed to fix it:
Create a dummy database with utf8mb4 ...
You could try turning on the general query log, which logs every query the server receives, and then use that information to see what's being queried, and then issue those queries yourself and examine the data.
To see the current setting for the general log (location and whether it's enabled):
mysql> SHOW VARIABLES LIKE 'general_log%'; # shows the on/...
There should be no space difference for VARCHAR and TEXT fields that contain 1- to 3-byte utf8 characters. CHAR is another matter.
A JOIN between utf8 (in one table) and utf8mb4 (in the other table) may lead to gross inefficiency. At least make sure you are consistent (both CHARACTER SET and COLLATION) on any columns that need to be compared to each other....
I assume you will do this for each table?
ALTER TABLE tbl CONVERT TO CHARACTER SET utf8mb4;
One potential issue involves the maximum of 767 bytes per column in an INDEX. If you currently have a VARHAR(255) latin1 field in an index, you will need to rethink it.
Decrease it to VARCHAR(191) if you are sure that 191 will suffice (into the future)
Use a '...
I made some attempts to dig into this issue, here are the results.
When you set a connection charset (i.e. SET NAMES utf8) MySQL transparently handle encoding conversion for you. For instance if I insert a à (\xE0 in latin1 \xC3A0 in utf8) in a latin1 table using a UTF8 connection it reads the UTF 8 value and store it in table as \xE0
mysql> SELECT HEX('...
Mojibake or double encoding.
Please provide SELECT col, HEX(col) FROM tbl WHERE ... to see whether the data is stored correctly. If correctly encoded for storage, è will be hex c3a8.
Don't run your app as root; it ignores init-connect, but you need the SET NAMES (or equivalent).