"howa" <howachen@gmail.com> wrote:
> Axel Schwenke =BCg=B9D=A1G
>
> 1. if we designed some columns might need to store UTF8 character,
> should we define the whole DB as UTF8, or just define UTF8 in the
> column definition? Which one is recommeded? for performance, for
> reliablity?

The encoding/collation is an attribute of the column. Each string-type
column has this attribute set when it is created. This attribute can
only be changed with an explicite ALTER TABLE statement. The encoding
setting of a column - and only this - determines how to store data in
this column.

The encoding/collation setting for table, database and server is just
a fallback that is used if you create a column without specifying an
encoding. You can change the encoding/collation setting of a table,
a database or the whole server - and no data will actually change.

The recommendation is to use an 8-bit encoding (and any collation)
for server, database and table. So if you forget to specify the
encoding for a new column, it will get a space-saving 8-bit encoding.
However, if your application demands utf8 for *all* columns anyway,
there is no problem to set the defaults to utf8.

Just keep in mind that utf8 is expensive. Use it - for columns -
only where neccessary. Setting just the table or database encoding
to utf8 has no performance implication at all.

> 2. Suppose my DB is defined as latin, but a table is UTF8, how to
> mysqlimport from a UTF8 text file to the UTF8 table? Currently, I found
> NO WAY to do so unless I alter the DB character set to UTF8. Since
> mysqlimport will simply ignore the table definition of UTF8, it will
> just use the DB default character set, and it is confusing.

If you use LOAD DATA INFILE regularly to load utf8 encoded data,
this would be a good reason to set the database encoding to utf8.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/