Req: Unicode usage example

[Mike J]

I have been searching through this forum and many other sources for some simplified examples to usage of unicode characters to get things to come out right.

Background:

(Unfortunately I cannot copy and paste from the dev machine because this is another machine than I am on right now so I'll do my best in being brief in descriptions).

Default settings on my setup of MySQL 5.0.19 (Win-NT) is UTF-8 charset

Database: DICTIONARY
Table: DICT

Create Table DICT(ID Integer Not Null Default 0,
KANJI varchar(50) Not Null Default '',
KANA varchar(50) Not Null Default '',
ROMAJI varchar(50) Not Null Default '',
ENGLISH varchar(500) Not Null Default '',
PRIMARY KEY(ID))Engine=InnoDB Default Charset=utf8;

When I insert data from Japanese UTF8 formatted text file it imports all data according to the response in the query from Load Text.

When I pull data to a web page using UTF8, EUC-JP, S-JIS or any other encoding all I get is char 63's (?????)

I am using UTF8 as I need to allow for this example to work with any language supported by MySQL so I can have other tables specifying english, latin, swedish, german, Russian, Chinese, Japanese, Korean and so on in the KANA, KANJI fields.
(Hence this is just an example and I decided to use a dictionary file for testing)

In order to simplify the response to my request, could someone well versed in unicode and MySQL post some example files for us novices that encompases the following scenarios? (I feel this would shorten the length of this forum thread :) )

1) How to create a table correctly that will hold all languages in some fields.
(and not only Japanese as in the sample table above)
2) How to issue a correct INSERT INTO query...
3) How to issue a correct SELECT * FROM query
4) How to issue a correct UPDATE query.

This I assume would help just about everyone in here, and of course me quite a lot.

Thanks in advance for all the helpful hints and tips in this and other threads.


Edit:
To simplify the intended usage I decided to add this edit here

The table I wish to form for use in an application is as follows

CREATE TABLE REF(ID Integer Not Null Auto_increment,
LANG Varchar(3) Not Null Default 'EN',
SUB Varchar(80) Not Null Default '',
CONTEXT Varchar(21000) Not Null Default '',
Primary Key (ID,LANG))Engine=InnoDB Default Charset=utf8;

In this way I can specify a help reference ID and a language abbreviation to call from any app or web page to display context sensitive subject and text to users of any language.

Perhaps this shows better the intended use of more than one language option in the database.



Edited 2 time(s). Last edit at 01/03/2007 02:34PM by Mike J.