Hot answers tagged

299

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): CREATE ...


70

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.


30

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 ...


19

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, ...


15

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&...


13

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 ...


11

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 ...


8

Think about it: You are storing data in the database as latin1 You are data is handled internally by mysqld as latin1 If data coming from the OS or from the connection is utf8, how is mysqld going to treat it? Rather than guessing or hoping for the best, you could change the incoming character set behavior. With the exception of information_schema and ...


7

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, ...


6

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 ...


6

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) speed 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 ...


5

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 (...


5

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): Combining Characters Consider having a lower-case ü ("u" with diaeresis) and an ...


4

The solution isn't precisely the same but this question is where I originally found direction for a similar issue and the concepts there should take you where you want to go. MySQL has a BINARY character set and from all appearances, by converting through it, you can prevent MySQL from realizing what you're actually doing and being "too helpful." Test case ...


4

After some trial and error, I've learned how and where to apply COLLATE: Converted lines like: SELECT SOMETHING FROM SOMEWHERE WHERE table_schema = given_database AND table_name = given_table AND index_name = given_index; To: SELECT SOMETHING FROM SOMEWHERE WHERE table_schema COLLATE utf8_unicode_ci = given_database AND table_name COLLATE ...


4

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 ...


4

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 β '); begin for i in 1..12 loop update example set t = t || t; end ...


4

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 ...


3

I just solved the charset problem by using Notepad++. The problem was that my php version is 4.5.5.1 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 ...


3

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.


3

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 ...


3

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: --nvarchar example UPDATE dbo.YourTable SET UnicodeColumn = STUFF(UnicodeColumn, 1, 1, '') WHERE CAST(LEFT(UnicodeColumn, 1) AS binary(2)) IN(...


3

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,...


3

POLITICALLY CORRECT ANSWER You cannot do MySQL Replication from a New Master to an Old Slave I have written many posts about this: Nov 26, 2012 : Replicate MySQL 5.0 Master to 5.5 Slave? Feb 08, 2012 : will replication from 5.5.20 to 5.0.XX server work? Dec 22, 2011 : What does the base64 BINLOG statements in mysqlbinlog output mean? Feb 04, 2011 : MySQL ...


3

There are three differences as far as I can tell (according to their documentation): Case-mappings (for LOWER() / UPPER() functions): https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html#charset-unicode-sets-uca The LOWER() and UPPER() functions perform case folding according to the collation of their argument. The difference between the two ...


2

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/...


2

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 ...


2

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....


2

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 '...


2

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('...


Only top voted, non community-wiki answers of a minimum length are eligible