phpbar.de logo

Mailinglisten-Archive

Re: y== im Schwedischen?
Archiv Mailingliste mysql-de

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: y== im Schwedischen?



Tobias Ratschiller schrieb:
> Huch! *Erschreck*:
> SQL-query INSERT INTO test (id, gg, ff, ee) VALUES ('mr', '', '', '')
> produced an error.
> MySQL said: Duplicate entry 'mr' for key 1
> 
> Das ist ein Fall fr Monty.

Oder zumindest fr eine verbesserte Dokumentation ...

Inzwischen hab' ich genauere Tests gemacht und durchblicke das jetzt
etwas besser.  Auch mit meiner Configure-Option "--with-charset=german1"
werden viele Zeichen bzgl. Sortierung und Duplikaterkennung als gleich
angesehen, nmlich:
     +------+------+------+------+
     | t1   | c1   | t2   | c2   |
     +------+------+------+------+
     |    0 |      |  160 |     |
     |   97 | a    |  224 |     |
     |   97 | a    |  225 |     |
     |   97 | a    |  226 |     |
     |   97 | a    |  227 |     |
     |   97 | a    |  228 |     |
     |   97 | a    |  229 |     |
     |   97 | a    |  230 |     |
     |   99 | c    |  231 |     |
     |  101 | e    |  232 |     |
     |  101 | e    |  233 |     |
     |  101 | e    |  234 |     |
     |  101 | e    |  235 |     |
     |  105 | i    |  236 |     |
     |  105 | i    |  237 |     |
     |  105 | i    |  238 |     |
     |  105 | i    |  239 |     |
     |  100 | d    |  240 |     |
     |  110 | n    |  241 |     |
     |  111 | o    |  242 |     |
     |  111 | o    |  243 |     |
     |  111 | o    |  244 |     |
     |  111 | o    |  245 |     |
     |  111 | o    |  246 |     |
     |  117 | u    |  249 |     |
     |  117 | u    |  250 |     |
     |  117 | u    |  251 |     |
     |  117 | u    |  252 |     |
     |  121 | y    |  253 |     |
     +------+------+------+------+
Auch Gro- und Kleinschreibung wird nicht unterschieden, aber das habe
der bersicht halber aus der Tabelle weggelassen.

Ich habe zum Test eine 2-spaltige Tabelle mit allen mglichen
Zeichenpaaren angelegt und aus dieser die Paare herausgeholt, die
MySQL als gleich ansieht.  Mit dem angehngten perl-Skript knnt
Ihr den SQL-Code zum Test erzeugen und es selbst ausprobieren.

   perl dupltest.pl | mysql test


Nach intensiven kaffeegesttze Handbuchrecherchen fand ich aber doch
eine Lsung: fr Textspalten gibt's das Attribut "BINARY"!

Hier gleich die passenden Passagen zitiert (Manual zu V3.22.19b):

| 1.4 The main features of MySQL
| [...]
|  * All data are saved in ISO-8859-1 Latin1 format. All comparisons for
|    normal string columns are case insensitive.
|  * Sorting is done according to the ISO-8859-1 Latin1 character set
|    (the Swedish way at the moment). It is possible to change this in
|    the source by adding new sort order arrays. To see an example of
|    very advanced sorting, look at the Czech sorting code. MySQL
|    supports many different character sets that can be specified at
|    compile time.

| 5.1 MySQL extensions to ANSI SQL92
| [...]
|  * All string comparisons are case insensitive by default, with
|    sort ordering determined by the current character set (ISO-8859-1
|    Latin1 by default). If you don't like this, you should declare your
|    columns with the BINARY attribute, which causes comparisons to be
|    done according to the ASCII order used on the MySQL server host.

| 4.7.3 Typical configure options
| [...]
|  * By default, MySQL uses the ISO-8859-1 (Latin1) character set. [...]
|    CHARSET may be one of big5, czech, danish, dec8, dos, german1,
|    hebrew, hp8, hungarian, koi8_ru, ru, latin1, latin2, sjis, swe7,
|    tis620, ujis, usa7 or win1251. [...]  Note that if you want to
|    change the character set, you must do a make distclean between
|    configurations!

| 9.1.1 The character set used for data and sorting
| 
| By default, MySQL uses the ISO-8859-1 (Latin1) character set. This is
| the character set used in the USA and western Europe.
|   The character set determines what characters are allowed in names
| and how things are sorted by the ORDER BY and GROUP BY clauses of the
| SELECT statement.
|   You can change the character set at compile time by using the
| --with-charset=charset option to configure. [...]

| 16.13 16.13 Case sensitivity in searches
| 
| By default, MySQL searches are case-insensitive (although there are
| some character sets that are never case insensitive, such as
| czech). That means that if you search with col_name LIKE 'a%', you
| will get all column values that start with A or a. If you want to make
| this search case-sensitive, use something like INDEX(col_name, "A")=0
| to check a prefix. Or use STRCMP(col_name, "A") = 0 if the column
| value must be exactly "A".
| 
| Simple comparison operations (>=, >, = , < , <=, sorting and grouping)
| are based on each character's "sort value". Characters with the same
| sort value (like E, e and 'e) are treated as the same character!
| 
| LIKE comparisons are done on the uppercase value of each character
| (E == e but E <> 'e)
| 
| If you want a column always to be treated in case-sensitive fashion,
| declare it as BINARY.

Offensichtlich ein Fall fr RTFM. :-)
Aber da die Hinweise im Manual sooo verstreut liegen, verzeiht Ihr
mir hoffentlich?

Da sich die Sortierreihenfolge aber auch auf die Duplicat-Erkennung
auswirkt, scheint nirgends erwhnt zu sein, so da hier aus meiner
Sicht offen ist, ob das nur ein schlecht dokumentiertes Feature oder
ein Bug ist.

Ciao,
  Martin
-- 
Martin Ramsch <m.ramsch_(at)_computer.org> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7

dupltest.pl


Home | Main Index | Thread Index

php::bar PHP Wiki   -   Listenarchive