Hot answers tagged

5

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


4

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


4

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


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

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html If you want to change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this: ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name; Follow the link for more details. Be sure to do this in a test ...


3

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


3

You may want to consider setting the database's default character for new tables going forward using ALTER DATABASE. Here is an example using MySQL 5.5.12 for Windows: mysql> show create database example; +----------+--------------------------------------------------------------------+ | Database | Create Database ...


2

Opinion It's probably better to throw an exception during your applications input checking and not pass the buck to the database. Workaround There is a "workaround" but your mileage may vary: http://forge.mysql.com/worklog/task.php?id=3780 Brute Force? You could convert your front end table VARCHAR field to a BLOB and store as binary data to cure the ...


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

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


2

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


2

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


2

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


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


2

Here is a summary of the utf8 collations: http://mysql.rjweb.org/utf8_collations.html It shows that 'n' and 'ñ' are considered equal for all but utf8_bin, utf8_spanish2_ci, and utf8_spanish_ci In the Spanish cases, Ñ and ñ are treated like a separate letter and sort after nz and before o. (Latvian and Polish have a couple of flavors of n that work like ...


2

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.


2

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


2

Depending on what RDBMS you are using: You can try altering the Latin1-encoded column to use an encoding of UTF-8, if the RDBMS supports such an operation. If you are using MySQL, you should look at this related Question on S.O.: Converting mysql tables from latin1 to utf8 You can try: Adding a new column with an encoding of UTF-8 UPDATE new_column = ...


2

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


1

This is a rather general question so my answer will be also quite general... Consolidate everything to UTF-8! If possible reload at least the latin1 entries: This time converting latin1 to UTF-8 which should always be possible. Depending on the database product the conversion can be performed by the import tool. Starting with flat files and a custom ...


1

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


1

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


1

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


1

Thanks for the minimal test case. Your TEXT column is declared to be latin1; there is no "dotless i", in latin1, so it was converted from utf8's hex C4B1 to ? during INSERT. Change the table declaration, preferably to utf8. Also remove SET CHARACTER SET utf8; -- It seems to hurt! If you wish to complain about the "silent" conversion, file a bug with http:/...


1

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


1

Since you said you can only use the Command prompt, my suggestion is to shift the character set to one that supports Arabic within the mysql client: I just ran these commands on my laptop at home (Windows 8.1) mysql> select * from information_schema.character_sets where description like '%arabic%'; +--------------------+----------------------+-----------...


1

so, I understand that you just want to remove '?' have you tried this: UPDATE tablename SET column=REPLACE(column,'?','');


1

Thank you very much - I found the solution. First, the output encoding of the Oracle command line tools can be controlled by either the NLS_LANG environment variable (in my situation, a value of GERMAN_GERMANY.AL32UTF8 was needed) or through the registry by the key HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\NLS_LANG Thank you very much for all - I leave this ...


1

From my experience storing such data, is it perfectly safe to transmit binary data when the character encoding of the connection is configured as UTF-8. The character encoding is just that: a way of representing the characters in binary form (this is the crux of the issue in Python bytes/str problems). MySQL rightly does not try to 'encode' binary data. ...


1

You'll have to specify a character set of UTF8 on the table schema. See http://dev.mysql.com/doc/refman/5.5/en/charset-syntax.html Depending on your needs you can specify table defaults which then apply to all unspecified text columns (char/varchar/text) or you can specify on a per column level. You'll also need to have your applications to specify a UTF-...



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