From: Rick James Date: October 1 2012 9:45pm Subject: RE: Need Help Converting Character Sets List-Archive: http://lists.mysql.com/mysql/228296 Message-Id: <2E7DD7ADE53B044C8C8BCD9C5829E1EB148CF91FDA@SP2-EX07VS01.ds.corp.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable (Sorry; these are random notes, not well structured for your questions.) &#...; -- This is an "html entity". It is plain ascii, but _represents_ (i= n an HTML contect) some special character. Some details: http://htmltimes.com/html-special-characters.php http://www.dwheeler.com/essays/quotes-in-html.html That is, $#...; is not a character set issue in MySQL. Most CHARACTER SETs= (latin1, utf8, etc) will handle them the same. And they will continue to = render on an HTML page as the 'funky characters'. That's HTML doing the de= ed in the browser. The ALTERs did nothing because there was nothing to do. I don't know what process converted from '"' to “. The utf8 equivalent: http://www.fileformat.info/info/unicode/char/201c/index.htm It would be hex e2809c (3 bytes). The "correct" way is to get the utf8 encoding from the
, and store th= at in a utf8-declared column in the table. If you already have utf8 bytes in your latin1 fields, it gets messier. hex 22 is the 'straight' ascii double quote. 8220 and 8221 are the "curved= " double quotes. A common convention is to translate both curved ones to t= he single straight one. But that would be work for you to explicitly do. If you are using PHP, see functions htmlentities() and html_entity_decode()= . > -----Original Message----- > From: Mark Phillips [mailto:mark@stripped] > Sent: Sunday, September 30, 2012 11:08 AM > To: Rick James > Cc: hsv@stripped; Mysql List > Subject: Re: Need Help Converting Character Sets >=20 > Thanks to all of you for your very helpful suggestions! I was pulled > off on a different project for a few days, and I am now just getting > back to this one. Sorry for my absence. >=20 > First, some more information: >=20 > My table definition: > CREATE TABLE `Articles` ( > `articleID` int(11) unsigned NOT NULL AUTO_INCREMENT, `title` > varchar(200) NOT NULL DEFAULT '', `author` varchar(200) NOT NULL > DEFAULT '', `body` text NOT NULL, `intro` text NOT NULL, `caption` text > NOT NULL, `credits` text NOT NULL, `articleDate` date NOT NULL DEFAULT > '0000-00-00', `imageTitle` varchar(255) NOT NULL DEFAULT '', > `imageAltText` varchar(255) NOT NULL DEFAULT '', `imageWidth` float NOT > NULL DEFAULT '1', `imageHeight` float NOT NULL DEFAULT '1', `imageFile` > varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`articleID`), FULLTEXT > KEY `search1` (`title`,`author`,`body`,`caption`,`credits`) > ) ENGINE=3DMyISAM AUTO_INCREMENT=3D89 DEFAULT CHARSET=3Dlatin1 >=20 > First problem - CHARSET should be utf8. >=20 > The data for this table comes from a web page (charet utf8). I > copy/paste word files into gedit (on linux) and then copy/paste from > gedit to a text boxes on the web page input form. I had thought I was > stripping out all the funky characters by using a simple ascii editor > like gedit, but obviously not. >=20 > After looking at the mysqldump for the table in a hex editor, I > discovered I have these characters scatter throughout the body and > intro columns: > “ > ” > ’ > — > … > ↩ >=20 > I tried converting the columns into utf8 with the command ALTER TABLE > CONVERT TO CHARACTER SET utf8; but all the special > characters are still there. >=20 > I tried converting to blob and back to utf8, and that didn't change > anything. (I had to first drop the fulltext key to convert to blob). > ALTER TABLE t1 CHANGE c1 c1 BLOB; > ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8; The above special > characters are still in the body column when I view the dump file with > a hex editor. >=20 > Is there a way to replace these special characters with the appropriate > utf8 characters (eg “ -> 22 hex) within the text columns body and > intro columns? >=20 > Thanks, >=20 > Mark >=20 > On Fri, Sep 28, 2012 at 7:59 AM, Rick James > wrote: >=20 > > Thanks for that link! That's another subtle issue I had not noted. > > > > There are so many combinations, that it is hard to say "do this": > > * Incoming bytes are latin1 / utf8 / Microsquish control characters. > > * You do/don't have SET NAMES (or equivalent) > > * The database/table/column is declared latin1/utf8/other. > > * The problem is on ingestion / on retrieval. > > > > The thing mentioned involved 2 steps: > > ALTER TABLE ... MODIFY COLUMN BINARY (or BLOB); -- to forget any > > charset knowledge ALTER TABLE ... MODIFY COLUMN CHARACTER SET ...; > > -- coming from BINARY, this does not check the encoding. > > (sorry, don't have the link handy) > > > > > -----Original Message----- > > > From: hsv@stripped [mailto:hsv@stripped] > > > Sent: Thursday, September 27, 2012 2:24 PM > > > To: Mark Phillips > > > Cc: Mysql List > > > Subject: Re: Need Help Converting Character Sets > > > > > > >>>> 2012/09/24 16:28 -0700, Mark Phillips >>>> > > > I have a table, Articles, of news articles (in English) with three > > > text columns for the intro, body, and caption. The data came from a > > > web page, and the content was cut and pasted from other sources. I > > > am finding that there are some non utf-8 characters in these three > > > text columns. I would like to (1) convert these text fields to be > > > strict > > > utf-8 and then (2) fix the input page to keep all new submissions > > > utf- 8. > > > > > > 91) For the first step, fixing the current database, I tried: > > > > > > update Articles set body =3D CONVERT(body USING ASCII); > > > > > > However, when I checked one of the articles I found an apostrophe > > > had been converted into a question mark. (FWIW, the apostrophe was > > > one of those offending non utf-8 characters): > > > > > > Before conversion: "I stepped into the observatory?s control room > ..." > > > > > > After conversion: "I stepped into the observatory?s control > room..." > > > > > > Is there a better way to accomplish my first goal, without reading > > > each article and manually making the changes? > > > <<<<<<<< > > > I do not remember where on the MySQL website this is, but there was > > > an article about converting from character sets in version 4 to > > > those in version 5, when UTF-8 first was supported. It sounds to me > > > that maybe the tricks shown there would be useful to you, since, in > > > effect, through MySQL MySQL was fooled into accepting for UTF-8 > that > > > which was not. Conversion to binary string was mentioned. > > > > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: http://lists.mysql.com/mysql > > > >