Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select MySQL rows with Japanese characters

Would anyone know of a reliable method (with mySQL or otherwise) to select rows in a database that contain Japanese characters? I have a lot of rows in my database, some of which only have alphanumeric characters, some of which have Japanese characters.

like image 201
Rio Avatar asked Mar 19 '11 06:03

Rio


2 Answers

Rules when you have problem with character sets:

  1. While creating database use utf8 encoding:

    CREATE DATABASE  _test DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
    
  2. Make sure all text fields (varchar and text) are using UTF-8:

    CREATE TABLE _test.test (
      id INT NOT NULL AUTO_INCREMENT,
      name VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE = MyISAM;
    
  3. When you make a connection do this before you query/update the database:

    SET NAMES utf8;
    
  4. With phpMyAdmin - Choose UTF-8 when you login.

  5. set web page encoding to utf-8 to make sure all post/get data will be in UTF-8 (or you'll have to since converting is painful..). PHP code (first line in the php file or at least before any output):

    header('Content-Type: text/html; charset=UTF-8');
    
  6. Make sure all your queries are written in UTF8 encoding. If using PHP:

6.1. If PHP supports code in UTF-8 - just write your files in UTF-8.

6.2. If php is compiled without UTF-8 support - convert your strings to UTF-8 like this:

    $str = mb_convert_encoding($str, 'UTF-8', '<put your file encoding here');
    $query = 'SELECT * FROM test WHERE name = "' . $str . '"';

That should make it work.

like image 63
NickSoft Avatar answered Oct 19 '22 01:10

NickSoft


Following on to the helpful answer NickSoft, i had to set the encoding on the db connection to get it to work.

&characterEncoding=UTF8

Then the SET NAMES utf8; seemed to be redundant

like image 40
mart Avatar answered Oct 19 '22 00:10

mart