Hi,

I found that even this settings of my MYSQL is set to
"latin1_swedish_ci", I can still insert and get back UTF-8 characters
in query. So what is reason?

Thanks...

howachen@gmail.com 寫�：

> Hi,
>
> I found that even this settings of my MYSQL is set to
> "latin1_swedish_ci", I can still insert and get back UTF-8 characters
> in query. So what is reason?
>
> Thanks...

some interesting findings...

using PHP,

1. if the default character set of a table is set to
"latin1_swedish_ci", then query can return the correct utf8 data in
browser

2. if the default character set of a table is set to "utf-8", then
query CANNOT return the correct utf8 data in browser, i need to perform
another query before the real query, i.e. set names 'utf8'

so why not use the "latin1_swedish_ci" ? no need to set names 'utf8'
on every php connection? isn't ?

howachen@gmail.com wrote:
> howachen@gmail.com 寫�：
>
>
>>Hi,
>>
>>I found that even this settings of my MYSQL is set to
>>"latin1_swedish_ci", I can still insert and get back UTF-8 characters
>>in query. So what is reason?
>>
>>Thanks...
>
>
> some interesting findings...
>
> using PHP,
>
> 1. if the default character set of a table is set to
> "latin1_swedish_ci", then query can return the correct utf8 data in
> browser
>
> 2. if the default character set of a table is set to "utf-8", then
> query CANNOT return the correct utf8 data in browser, i need to perform
> another query before the real query, i.e. set names 'utf8'
>
> so why not use the "latin1_swedish_ci" ? no need to set names 'utf8'
> on every php connection? isn't ?
>

MySQL, like all RDB's, just store bytes. You can put about anything into them.
But the collation has to do with the way it handles data in the ORDER clause
(collates).

See http://dev.mysql.com/doc/refman/5.1/...on-effect.html for an
example.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle 寫�：

> howachen@gmail.com wrote:
> > howachen@gmail.com 寫�：
> >
> >
> >>Hi,
> >>
> >>I found that even this settings of my MYSQL is set to
> >>"latin1_swedish_ci", I can still insert and get back UTF-8 characters
> >>in query. So what is reason?
> >>
> >>Thanks...
> >
> >
> > some interesting findings...
> >
> > using PHP,
> >
> > 1. if the default character set of a table is set to
> > "latin1_swedish_ci", then query can return the correct utf8 data in
> > browser
> >
> > 2. if the default character set of a table is set to "utf-8", then
> > query CANNOT return the correct utf8 data in browser, i need to perform
> > another query before the real query, i.e. set names 'utf8'
> >
> > so why not use the "latin1_swedish_ci" ? no need to set names 'utf8'
> > on every php connection? isn't ?
> >
>
> MySQL, like all RDB's, just store bytes. You can put about anything intothem.
> But the collation has to do with the way it handles data in the ORDER clause
> (collates).
>
> See http://dev.mysql.com/doc/refman/5.1/...on-effect.html for an
> example.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstucklex@attglobal.net
> ==================

so it that beside the "order clause" issue, there is no difference?

howachen@gmail.com wrote:
> Jerry Stuckle 寫�：
>
>
>>howachen@gmail.com wrote:
>>
>>>howachen@gmail.com 寫�：
>>>
>>>
>>>
>>>>Hi,
>>>>
>>>>I found that even this settings of my MYSQL is set to
>>>>"latin1_swedish_ci", I can still insert and get back UTF-8 characters
>>>>in query. So what is reason?
>>>>
>>>>Thanks...
>>>
>>>
>>>some interesting findings...
>>>
>>>using PHP,
>>>
>>>1. if the default character set of a table is set to
>>>"latin1_swedish_ci", then query can return the correct utf8 data in
>>>browser
>>>
>>>2. if the default character set of a table is set to "utf-8", then
>>>query CANNOT return the correct utf8 data in browser, i need to perform
>>>another query before the real query, i.e. set names 'utf8'
>>>
>>>so why not use the "latin1_swedish_ci" ? no need to set names 'utf8'
>>>on every php connection? isn't ?
>>>
>>
>>MySQL, like all RDB's, just store bytes. You can put about anything into them.
>> But the collation has to do with the way it handles data in the ORDER clause
>>(collates).
>>
>>See http://dev.mysql.com/doc/refman/5.1/...on-effect.html for an
>>example.
>>
>>--
>>==================
>>Remove the "x" from my email address
>>Jerry Stuckle
>>JDS Computer Training Corp.
>>jstucklex@attglobal.net
>>==================
>
>
> so it that beside the "order clause" issue, there is no difference?
>

That's what the page says.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

>> > 1. if the default character set of a table is set to
>> > "latin1_swedish_ci", then query can return the correct utf8 data in
>> > browser
>> >
>> > 2. if the default character set of a table is set to "utf-8", then
>> > query CANNOT return the correct utf8 data in browser, i need to perform
>> > another query before the real query, i.e. set names 'utf8'
>> >
>> > so why not use the "latin1_swedish_ci" ? no need to set names 'utf8'
>> > on every php connection? isn't ?
>> >
>>
>> MySQL, like all RDB's, just store bytes. You can put about anything
>into them.
>> But the collation has to do with the way it handles data in the ORDER clause
>> (collates).
>>
>> See
>http://dev.mysql.com/doc/refman/5.1/...on-effect.html for
>an
>> example.
>>
....
>
>so it that beside the "order clause" issue, there is no difference?

I would assume that any operation that compares strings for other
than equality/inequality would be (at least potentially) be affected
by the collation, for instance
... WHERE a > b AND ...
with a and b being VARCHAR fields. ORDER BY is of course the main one.

Gordon L. Burditt

Gordon Burditt wrote:
>>>>1. if the default character set of a table is set to
>>>>"latin1_swedish_ci", then query can return the correct utf8 data in
>>>>browser
>>>>
>>>>2. if the default character set of a table is set to "utf-8", then
>>>>query CANNOT return the correct utf8 data in browser, i need to perform
>>>>another query before the real query, i.e. set names 'utf8'
>>>>
>>>>so why not use the "latin1_swedish_ci" ? no need to set names 'utf8'
>>>>on every php connection? isn't ?
>>>>
>>>
>>>MySQL, like all RDB's, just store bytes. You can put about anything
>>
>>into them.
>>
>>> But the collation has to do with the way it handles data in the ORDER clause
>>>(collates).
>>>
>>>See
>>
>>http://dev.mysql.com/doc/refman/5.1/...on-effect.html for
>>an
>>
>>>example.
>>>
>
> ...
>
>>so it that beside the "order clause" issue, there is no difference?
>
>
> I would assume that any operation that compares strings for other
> than equality/inequality would be (at least potentially) be affected
> by the collation, for instance
> ... WHERE a > b AND ...
> with a and b being VARCHAR fields. ORDER BY is of course the main one.
>
> Gordon L. Burditt

Gordon,

I guess I wasn't clear. Yes, any comparisons like this are affected.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Gordon Burditt 寫�：

> >> > 1. if the default character set of a table is set to
> >> > "latin1_swedish_ci", then query can return the correct utf8 data in
> >> > browser
> >> >
> >> > 2. if the default character set of a table is set to "utf-8", then
> >> > query CANNOT return the correct utf8 data in browser, i need to perform
> >> > another query before the real query, i.e. set names 'utf8'
> >> >
> >> > so why not use the "latin1_swedish_ci" ? no need to set names 'utf8'
> >> > on every php connection? isn't ?
> >> >
> >>
> >> MySQL, like all RDB's, just store bytes. You can put about anything
> >into them.
> >> But the collation has to do with the way it handles data in the ORDER clause
> >> (collates).
> >>
> >> See
> >http://dev.mysql.com/doc/refman/5.1/...on-effect.html for
> >an
> >> example.
> >>
> ...
> >
> >so it that beside the "order clause" issue, there is no difference?
>
> I would assume that any operation that compares strings for other
> than equality/inequality would be (at least potentially) be affected
> by the collation, for instance
> ... WHERE a > b AND ...
> with a and b being VARCHAR fields. ORDER BY is of course the main one.
>
> Gordon L. Burditt

however, comparsion/ordering on characters "might" not make sense on
other language such as Japanese or Chinese (in fact, event for english,
how many time your application would compare "apple" > "orange" ? i
doubt)

so, collation is only useful for western language i think. setting
collation to latin is okay for some languages.

howachen@gmail.com wrote:
>> [Collation]
>
> so it that beside the "order clause" issue, there is no difference?

UNIQUE keys also behave differently between a case-sensitive and a
case-insensitive Collation. "collation" and "Collation" can't be both in
a case-insensitive UNIQUE-Column, but in a case-sensitive one they could.

Cheers,
--
Philipp Tölke
PGP 0x96A1FE7A

gordonb.0s4w1@burditt.org (Gordon Burditt) wrote:

>>so it that beside the "order clause" issue, there is no difference?
>
> I would assume that any operation that compares strings for other
> than equality/inequality would be (at least potentially) be affected
> by the collation, for instance
> ... WHERE a > b AND ...
> with a and b being VARCHAR fields. ORDER BY is of course the main one.

Collations even affect equality tests. Most collations (in MySQL) come
in two variants: *_ci = case insensitive and *_cs = case sensitive.

So SELECT 'Apple' = 'apple' may be true or false, depending on the
collation used.

Some western languages have quite surprising collation rules. I.e. the
(MySQL default) Swedish collation defines 'y' = 'ü'. One of the German
collations defines 'a' = 'ä', 'o' = 'ö' and so on.

So setting the correct collation is mandatory. Even if you live in a
part of the world where collation rules are simple.


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/