Professional Web Applications Themes

Is "Collation" in MySQL useful? - MySQL

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......

  1. #1

    Default Is "Collation" in MySQL useful?

    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 Guest

  2. #2

    Default Re: Is "Collation" in MySQL useful?


    [email]howachen[/email] 寫道:
    > 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 Guest

  3. #3

    Default Re: Is "Collation" in MySQL useful?

    [email]howachen[/email] wrote:
    > [email]howachen[/email] 寫道:
    >
    >
    >>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 [url]http://dev.mysql.com/doc/refman/5.1/en/cht-collation-effect.html[/url] for an
    example.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  4. #4

    Default Re: Is "Collation" in MySQL useful?


    Jerry Stuckle 寫道:
    > [email]howachen[/email] wrote:
    > > [email]howachen[/email] 寫道:
    > >
    > >
    > >>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 [url]http://dev.mysql.com/doc/refman/5.1/en/cht-collation-effect.html[/url] for an
    > example.
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > [email]jstucklexattglobal.net[/email]
    > ==================
    so it that beside the "order clause" issue, there is no difference?

    howachen@gmail.com Guest

  5. #5

    Default Re: Is "Collation" in MySQL useful?

    [email]howachen[/email] wrote:
    > Jerry Stuckle 寫道:
    >
    >
    >>howachen wrote:
    >>
    >>>howachen 寫道:
    >>>
    >>>
    >>>
    >>>>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 [url]http://dev.mysql.com/doc/refman/5.1/en/cht-collation-effect.html[/url] for an
    >>example.
    >>
    >>--
    >>==================
    >>Remove the "x" from my email address
    >>Jerry Stuckle
    >>JDS Computer Training Corp.
    >>jstucklexattglobal.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.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  6. #6

    Default Re: Is "Collation" in MySQL useful?

    >> > 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
    >[url]http://dev.mysql.com/doc/refman/5.1/en/cht-collation-effect.html[/url] 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 Guest

  7. #7

    Default Re: Is "Collation" in MySQL useful?

    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
    >>
    >>[url]http://dev.mysql.com/doc/refman/5.1/en/cht-collation-effect.html[/url] 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.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  8. #8

    Default Re: Is "Collation" in MySQL useful?


    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
    > >[url]http://dev.mysql.com/doc/refman/5.1/en/cht-collation-effect.html[/url] 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 Guest

  9. #9

    Default Re: Is "Collation" in MySQL useful?

    [email]howachen[/email] 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
    Philipp Tölke Guest

  10. #10

    Default Re: Is "Collation" in MySQL useful?

    [email]gordonb.0s4w1burditt.org[/email] (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: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Axel Schwenke Guest

  11. #11

    Default Re: Is "Collation" in MySQL useful?


    Axel Schwenke 寫道:
    > [email]gordonb.0s4w1burditt.org[/email] (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: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    > MySQL User Forums: [url]http://forums.mysql.com/[/url]
    hi,

    so can i say that:

    collation in mysql is useful for string comparing

    but character set definition in table creation is optional? (since
    mysql store string as binary)

    howachen@gmail.com Guest

  12. #12

    Default Re: Is "Collation" in MySQL useful?

    > so can i say that:
    >
    > collation in mysql is useful for string comparing
    >
    > but character set definition in table creation is optional? (since
    > mysql store string as binary)
    Yes and No. In fact specifying the character set is not necessary in
    MySQL when you specify the collation. This is because each collation
    implies a certain character set. However "mysql store string as binary"
    is not true. It's not even grammatically correct. Also the term
    /binary/ has a special meaning for databases. It means the absence of
    both a character set and a collation.

    In a nutshell: a /character set/ defines a set of glyphs and a set of
    numbers and associates each glyph with exactly one number (called the
    character code). Examples: latin1 (ISO8859-1), Unicode (ISO10646).

    A /collation/ defines sorting and comparison rules for strings (which
    are in turn concatenations of glyphs). Since computers work with
    numbers (not glyphs) practical collations work with the associated
    numbers (this association is given by the character set). So a
    computer implementation of a collation is specific for the character
    set used.

    Typically collations are associated with cultural entities, i.e. spoken
    languages. Germany and (parts of) Switzerland speak the same language,
    but use different sort order in i.e. phone books. In Germany there are
    different sort orders for phone books and dictionaries. All western
    countries share the latin1 character set, but use different collation
    rules. So in praxis each collation is bound to a specific character set
    and there is more than one collation for a certain character set.

    Since databases don't deal with glyphs, they are not interested in
    character sets for all internal operations (comparing/sorting).
    However character sets (also called /encoding/) become interesting
    when dealing with the outside world. So a database may store all data
    in Unicode internally but convert to latin1 when it speaks to the
    client. Speaking for MySQL: beginning with 4.1 the client can specify
    which encoding it uses to send data to the database and which encoding
    the database should use for data sent to the client. Both may differ
    from each other and also from the internal encoding. In case of
    differences MySQL will convert the data.

    For more details, what MySQL supports and what not, please consult the
    online manual: [url]http://dev.mysql.com/doc/refman/5.0/en/cht.html[/url]


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

    Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Axel Schwenke Guest

  13. #13

    Default Re: Is "Collation" in MySQL useful?


    Axel Schwenke 寫道:
    > Since databases don't deal with glyphs, they are not interested in
    > character sets for all internal operations (comparing/sorting).
    > However character sets (also called /encoding/) become interesting
    > when dealing with the outside world. So a database may store all data
    > in Unicode internally but convert to latin1 when it speaks to the
    > client. Speaking for MySQL: beginning with 4.1 the client can specify
    > which encoding it uses to send data to the database and which encoding
    > the database should use for data sent to the client. Both may differ
    > from each other and also from the internal encoding. In case of
    > differences MySQL will convert the data.
    >
    > For more details, what MySQL supports and what not, please consult the
    > online manual: [url]http://dev.mysql.com/doc/refman/5.0/en/cht.html[/url]
    >
    thanks for your lengthly reply!

    my last thought:

    is that mean no matter the character set you specify in table creation,
    mysql has it own standard internal format for storing string.

    the default character set of a DB/table/row is used to make connection
    to client if the client doesn't specify encoding needed, MySQL will use
    this default value

    Thanks again!

    howachen@gmail.com Guest

  14. #14

    Default Re: Is "Collation" in MySQL useful?

    [email]howachen[/email] wrote:
    > my last thought:
    >
    > is that mean no matter the character set you specify in table creation,
    > mysql has it own standard internal format for storing string.
    No. MySQL stores strings using the character set associated with the
    column. Every string type column has an associated character set
    (except BINARY strings). The character set chosen for a column limits
    the characters that can be stored.
    > the default character set of a DB/table/row is used to make connection
    > to client if the client doesn't specify encoding needed, MySQL will use
    > this default value
    No. Did you read chapter 10 of the manual? Defaults are used if you
    create an object without specifying a cht (or collation). I.e.
    the table default is used if you add a string type column without
    specifying a cht (or collation).

    Client/Connection chts are separate. You can change them any time
    during a MySQL session. The default client cht is guessed from your
    operating environment.


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

    Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Axel Schwenke Guest

  15. #15

    Default Re: Is "Collation" in MySQL useful?


    Axel Schwenke 寫道:
    > [email]howachen[/email] wrote:
    >
    > > my last thought:
    > >
    > > is that mean no matter the character set you specify in table creation,
    > > mysql has it own standard internal format for storing string.
    >
    > No. MySQL stores strings using the character set associated with the
    > column. Every string type column has an associated character set
    > (except BINARY strings). The character set chosen for a column limits
    > the characters that can be stored.
    >
    this made me confused...even if i use latin as the charcter set, i can
    insert UTF8 Chinese character into the table and correctly get it back.

    howachen@gmail.com Guest

  16. #16

    Default Re: Is "Collation" in MySQL useful?

    [email]howachen[/email] wrote:
    > Axel Schwenke =E5=AF=AB=E9=81=93=EF=BC=9A
    >>
    >> MySQL stores strings using the character set associated with the
    >> column. Every string type column has an associated character set
    >> (except BINARY strings). The character set chosen for a column limits
    >> the characters that can be stored.
    >
    > this made me confused...even if i use latin as the charcter set, i can
    > insert UTF8 Chinese character into the table and correctly get it back.
    This is because your client lies about it's encoding. Try

    mysql>SHOW VARIABLES LIKE 'character_set_client';

    this gives you 'latin1', right? However your client uses utf8. Since
    each utf8 character is a sequence of bytes, and each byte is a valid
    latin1 character, MySQL cannot tell if the byte sequence 0xC3, 0xBC
    is character '' (u with diaresis) in utf8 encoding or if it is 'ü'
    (upper case a with tilde accent, followed by the one-quarter sign)
    in latin1 encoding.

    Now correct your client encoding to utf8:

    mysql>SET NAMES utf8;

    Then select your data again. The chinese characters are gone and you
    see a strange sequence of latin1 characters, right? If you try

    mysql>SELECT CONVERT(<column> USING utf8);

    the Chinese characters are there again? Then your MySQL is working.

    You may like to play with MySQLs HEX(), UNHEX(), CHAR() and CONVERT()
    functions in order to learn more about character encoding.


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

    Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Axel Schwenke Guest

  17. #17

    Default Re: Is "Collation" in MySQL useful?


    Axel Schwenke 寫道:
    > [email]howachen[/email] wrote:
    > > Axel Schwenke =E5=AF=AB=E9=81=93=EF=BC=9A
    > >>
    > >> MySQL stores strings using the character set associated with the
    > >> column. Every string type column has an associated character set
    > >> (except BINARY strings). The character set chosen for a column limits
    > >> the characters that can be stored.
    > >
    > > this made me confused...even if i use latin as the charcter set, i can
    > > insert UTF8 Chinese character into the table and correctly get it back.
    >
    > This is because your client lies about it's encoding. Try
    >
    > mysql>SHOW VARIABLES LIKE 'character_set_client';
    >
    > this gives you 'latin1', right? However your client uses utf8. Since
    > each utf8 character is a sequence of bytes, and each byte is a valid
    > latin1 character, MySQL cannot tell if the byte sequence 0xC3, 0xBC
    > is character 'ü' (u with diaresis) in utf8 encoding or if it is 'ü'
    > (upper case a with tilde accent, followed by the one-quarter sign)
    > in latin1 encoding.
    >
    > Now correct your client encoding to utf8:
    >
    > mysql>SET NAMES utf8;
    >
    > Then select your data again. The chinese characters are gone and you
    > see a strange sequence of latin1 characters, right? If you try
    >
    > mysql>SELECT CONVERT(<column> USING utf8);
    >
    > the Chinese characters are there again? Then your MySQL is working.
    >
    > You may like to play with MySQLs HEX(), UNHEX(), CHAR() and CONVERT()
    > functions in order to learn more about character encoding.
    >
    >
    > XL
    > --
    > Axel Schwenke, Senior Software Developer, MySQL AB
    >
    > Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    > MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Thanks for you excellent explainations!!!

    howachen@gmail.com Guest

Similar Threads

  1. Replies: 1
    Last Post: April 24th, 01:27 PM
  2. Replies: 0
    Last Post: November 26th, 10:33 PM
  3. CFINPUT type="radio" w/ "value" requires "label"
    By Iceborer in forum Macromedia ColdFusion
    Replies: 2
    Last Post: February 21st, 06:16 PM
  4. #12029 [Com]: "--with-apxs" can cause the error "MySQL: Unable to save result set in"
    By sietzepost at streetwisedd dot nl in forum PHP Development
    Replies: 1
    Last Post: October 3rd, 09:21 AM
  5. "Start" "Program" "Menu" list is empty
    By Pete in forum Windows XP/2000/ME
    Replies: 2
    Last Post: July 10th, 10:42 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139