Verknüpfung möglich ?

[Martin Hein]

Hallo Forum,

ich habe in einer MySQL-Tabelle content ein Feld service\_ids,
in dem durch Komma getrennt mehrere Ziffern stehen (z.B. 1,12,5,6').
Das Feld ist vom Typ 'varchar'.

In einer anderen Tabelle service gibt es ein Feld id vom Typ
'int'.

In einem SELECT-Statement würde ich diese beiden Tabellen über
JOIN verknüpfen. Wenn in dem Feld content.service\_ids eine
Ziffer vom Typ int stünde, wäre das ja kein Problem, aber so
weiss ich nicht so genau, wie.

Geht das grundsätzlich ? Und wenn ja dann wie ?

danke für Tipps und

beste gruesse,
martin

[Cheatah]

Hi,

In einem SELECT-Statement würde ich diese beiden Tabellen über
JOIN verknüpfen. Wenn in dem Feld content.service\_ids eine
Ziffer vom Typ int stünde, wäre das ja kein Problem, aber so
weiss ich nicht so genau, wie.

Geht das grundsätzlich ?

nein. In der einen Tabelle hast Du Zahlen stehen, in der anderen Zeichenkette. Betrachte diese als *nicht* auftrennbar - Daten in einer Tabellenzelle sind atomar.

Das DB-Layout ist defekt. Normalisiere die Daten in einer 1:n- bzw. n:m-Beziehung.

Cheatah

[Martin Hein]

Hallo,

ich geb euch beiden vollkommen Recht. Das DB-Design ist gewachsen
und in dem Fall wild gewachsen. Dennoch funktioniert's so und
das DB-Design nun anzupassen ist zeitlich nicht drin.

Die Aufgabe vor der ich stehe, ist lediglich einen Datenauszug
zu schaffen, der ihm als Übersicht dienen soll. Ich hätte das
gerne unter PhpMyAdmin mit einem SELECT-Statement gemacht, wenn
sich das aber so nicht formulieren lässt, kann ich auch eben ein
PHP-Script schreiben, dass mir diese Übersicht erstellt.

beste gruesse,
martin

[Vinzenz Mai]

Hallo Martin,

es ist immer eine gute Idee, solche Randbedingungen anfangs zu erwähnen.

ich geb euch beiden vollkommen Recht. Das DB-Design ist gewachsen
und in dem Fall wild gewachsen. Dennoch funktioniert's so und
das DB-Design nun anzupassen ist zeitlich nicht drin.

Die Aufgabe vor der ich stehe, ist lediglich einen Datenauszug
zu schaffen, der ihm als Übersicht dienen soll.

doch, doch, das geht. Und zwar mit der von Tom genannten - aber falsch verlinkten - Funktion find_in_set().

Für die schnelle Aufgabenerledigung ist das angemessen, auf Dauer wird man mit
einem solchen Tabellendesign jedoch nicht glücklich.

Freundliche Grüße

Vinzenz

[Martin Hein]

Hallo Vinzenz

prima, danke für die Info. Dann werd eich doch nochmal versuchen,
ein SQL-Statement zu entwickeln, dass mir diesen Datenauszug
erstellt. Schöne Aufgabe.

Im Moment (bevor ich irgendwas verknüpft habe) scheitert es aber
noch (was mit LOCATE() nicht passiert) ;)

SQL-Befehl:

SELECT
content\_offline.link\_title ,
content\_offline.exlinks\_ids ,
FIND_IN_SET( '1', content\_offline.exlinks\_ids ) AS found
FROM content\_offline
LIMIT 0 , 3000

MySQL meldet:
#1267 - Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'find_in_set'

Das mit den Schriftsätzen habe ich noch lange nicht verstanden ;(

beste gruesse,
martin

[Tom]

Hello,

Das mit den Schriftsätzen habe ich noch lange nicht verstanden ;(

Schriftsätze gibt's, glaube ich, beim Rechtsanwalt...
Aber verstanden habe ich genau diese Sache bei MySQL auch noch nicht.
Hatte ich gestern nämlich auch und viiiile viele Andere im Web auch. Und die Lösungsansätze scheinen alle falsch zu sein. Nur irgendwann heute Nacht dachte ich dann: Was 30mal falsch im Web steht (und kein einziges Mal richtig), das könnte ja vielleicht soch zutreffend sein und _ich_ bin derjenige, der es verkehrt versteht...

Es war aber trotzdem falsch, auch wenn ich frustiert anfing zu glauben, was da sooft beschreiben stand...

Und nachher hatte ich soviel im Kreis rum geändert, dass sich das Problem von alleine gelöst hat - oder?

siehe https://forum.selfhtml.org/?t=162572&m=1057987

Wäre als ganz klasse, wenn sich nochmal jemand dieses Phänomens annehmen könnte.

Harzliche Grüße vom Berg
http://bergpost.annerschbarrich.de

Tom

[dedlfix]

echo $begrüßung;

FIND_IN_SET( '1', content\_offline.exlinks\_ids )
#1267 - Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'find_in_set'

Es sieht so aus, als ob für das Feld content\_offline.exlinks\_ids als Kodierung/Kollation latin1_swedish_ci eingestellt ist, die derzeitige Verbindung, über die das Statement abgesetzt wurde, auf utf8_general_ci steht. Für den String '1' wird die Kodierung/Kollation der Verbindung genommen, für das Feld die dafür eingestellte. Und nun gibt es damit ein Problem, weil MySQL Äpfel mit Birnen vergleichen soll, das aber nicht kann. (Da es mir leider nicht gelingt, den Fehler nachzustellen, bitte ich um die genaue Angabe der Server-Version (SELECT VERSION()) und des CREATE-Statements der Tabelle.)

Es empfiehlt sich, durchgehend mit der gleichen Kodierung zu arbeiten, weil sonst Datenverluste beim Umkodieren entstehen können (wenn die Ziel-Kodierung nicht alle Zeichen der Ausgagskodierung darstellen kann). Ebenfalls wichtig ist, dass alle Beteiligten genau wissen, welche Kodierung vorliegt und dann damit auch umgehen können.

Das mit den Schriftsätzen habe ich noch lange nicht verstanden ;(

Da fehlt erstmal grundlegendes Verständnis über die Darstellung von Zeichen in der elektronischen Datenverarbeitung. Grundlagen kannst du dir anlesen beispielsweise im SELFHTML-Kapitel Computer und geschriebene Sprache oder auch in jenem Forumsbeitrag von mir.

Seit Version 4.1 hat MySQL ein komplexes System der Kodierungen/Kollationen erhalten.
Die Kodierung ist zuständig, den Zeichen bestimmte (Byte-)Werte zuzuordnen, um sie computergerecht speichern und verarbeiten zu können. Eine Kollation gibt Vergleichsregeln an. Jede Sprache hat da mehr oder weniger ihre eigenen Regeln, manchmal auch mehrere (z.B. ß=ss oder ß=s).

In einem MySQL-Server gibt es (wenn ich richtig gezählt habe) 10 verschieden_artige_ Stellen [*], an denen eine Kodierung oder Kollation angegeben werden kann. Die wichtigsten sind die Angaben der einzelnen Felder, denn damit legt man fest, welche Zeichen gespeichert werden können. Beispielsweise lassen sich mit Latin1 keine griechischen Buchstaben abbilden. Nimmt man aber UTF-8 kann man praktisch alle Zeichen der Welt verwenden.

Es nützt aber nichts, diese Zeichen nur speichern zu können, man muss sie auch von und zum MySQL-Server transportieren können. Dafür sollte man für jede eröffnete Verbindung explizit eine Kodierung einstellen und sich nicht auf irgendwelche Server-Default-Werte verlassen. Mit dem Statement SET NAMES utf8 legt man UTF-8 als Kodierung für die Verbindung fest. MySQL erwartet dann alle Werte in Statements in UTF-8-Kodierung, und gibt alle Resultate UTF-8-kodiert an den Client zurück. Wenn für die Abfrage beteiligte Tabellenfelder eine andere Kodierung aufweisen, versucht MySQL selbständig umzukodieren. Das geht aber technisch bedingt nicht immer für alle Zeichen, nur für die, die in der Zielkodierung darstellbar sind. Die restlichen Zeichen gehen verloren.

Weniger wichtig sind die Kodierungs-/Kollationsangaben für Tabellen und Datenbanken, denn das sind nur Default-Werte, die verwendet werden, wenn für Felder keine explizite Angabe gemacht wurde. Die Default-Werte "vererben" sich in dieser Reihenfolge: Server-Defaultwert -> Datenbank-Defaultwert -> Tabellen->Defaultwert -> Feldkodierung.

[*] Alle Stellen zu finden lasse ich mal als Hausaufgabe (besonders für Tom) stehen :-)

Achja, das MySQL-Handbuch wurde die Tage erst umgestrickt, so dass das frühere Haupt-Kapitel Character Set Support nun ein Unterkapitel von Internationalization and Localization geworden ist. Trotzdem empfiehlt es sich, diese(s) Kapitel nicht unbeachtet zu lassen.

echo "$verabschiedung $name";

[Tom]

Hello,

Es nützt aber nichts, diese Zeichen nur speichern zu können, man muss sie auch von und zum MySQL-Server transportieren können. Dafür sollte man für jede eröffnete Verbindung explizit eine Kodierung einstellen und sich nicht auf irgendwelche Server-Default-Werte verlassen. Mit dem Statement SET NAMES utf8 legt man UTF-8 als Kodierung für die Verbindung fest.

Das heißt also, dass ich, als chronischer utf-8-Verweigerer da als erstes Statement in PHP immer "SET NAMES latin1;" vorausschicken sollte, wenn ich eine Datenbank(server)verbindung hergestellt habe. Macht man das vor oder nach dem mysql_select_db() oder ist es egal? (Die Datenbanknamen haben bei mir keine Zeichen oberhalb 7-Bit).

Weniger wichtig sind die Kodierungs-/Kollationsangaben für Tabellen und Datenbanken, denn das sind nur Default-Werte, die verwendet werden, wenn für Felder keine explizite Angabe gemacht wurde. Die Default-Werte "vererben" sich in dieser Reihenfolge: Server-Defaultwert -> Datenbank-Defaultwert -> Tabellen->Defaultwert -> Feldkodierung.

Dann kann ich das aber eventuell nachvollziehen.
Meine alte DB war angelegt auf latin1. Ich habe einen Dump gezogen und diesen importiert in die neue Datenbanksammlung. Da war nur ein bisschen Quark drin, eine Testdatenbank mit irrelevanten Daten, eben zum Spielen. Die "Sammlung" stand aber nun auf utf-8.

Beim Import muss da irgendwas schiefgegangen sein.
Ich hab dann später auf latin1 zurückgestellt und dann nacheinander alle möglichen "mixed-Muster" erhalten. Erst nachdem ich wirklich für alle Felder aller Tabellen mit dem Frontend wieder explizit  "latin1" und vor allem die Kollation "latin1-general-ci" eingestllt hatte, war der Fehler weg.

Es sind auch keine Datenverluste zu beklagen.

[*] Alle Stellen zu finden lasse ich mal als Hausaufgabe (besonders für Tom) stehen :-)

Das habe ich befürchtet, habe es aber schon auf dem Zettel... :-)

Harzliche Grüße vom Berg
http://bergpost.annerschbarrich.de

Tom

[dedlfix]

echo $begrüßung;

Das heißt also, dass ich, als chronischer utf-8-Verweigerer da als erstes Statement in PHP immer "SET NAMES latin1;" vorausschicken sollte, wenn ich eine Datenbank(server)verbindung hergestellt habe. Macht man das vor oder nach dem mysql_select_db() oder ist es egal? (Die Datenbanknamen haben bei mir keine Zeichen oberhalb 7-Bit).

Normalerweise empfiehlt es sich unmittelbar nach dem Connect ein mysql_query() mit SET NAMES abzusetzen. Wenn man etwas fortgeschrittener ist, nimmt man ja vielleicht eine Datenbank-Klasse, da kann man das gleich als einen der Connection-Parameter mitgeben und in die die Verbindung aufbauende Methode integrieren (ggf. abhängig von der MySQL-Server-Version (Test ob 4.1 oder größer vorliegt)).

Da du aber ein pingeliger Mensch bist, müsstest du korrekterweise auf SET NAMES und die alten mysql-Funktionen von PHP verzichten, denn ein SET NAMES wirkt sich zwar auf die über diese Verbindung transportierten Daten aus, nicht aber auf die MySQL-API-Funktion mysql_real_escape_string() und dessen PHP-Pendant. Da wäre der richtige Weg, die mysqli-Schnittstelle zu verwenden und mit mysqli_set_charset() die Verbindungskodierung zu setzen. (Wie ich gerade sehe, hat ab PHP 5.2.3 mittlerweile auch in der alten mysql-Funktionen-Welt eine Funktion namens mysql_set_charset() Einzug gehalten, die mysqli_set_charset() entspricht.)

Alle weniger pingeligen können weiterhin mysql_real_escape_string() und sogar auch mysql_escape_string() verwenden, denn die Bytewerte der von diesen Funktionen behandelten Zeichen sind für die im alten Europa üblichen Kodierungen ISO-8859-x und UTF-8 eineindeutig und die selben, da sie alle im Bereich 0x00 bis 0x7F liegen. Es gibt mindestens[*] eine in Asien verwendete Kodierung, bei der mindestens[*] ein Bytewert eines der fraglichen Zeichen in Bytesequenzen anderer Zeichen verwendet wird. Dann erzeugt mysql_(real_)escape_string() eine falsche Maskierung, und das trotz SET NAMES, wenn dieser Wert vom Server-Default-Wert abweicht.

[*] "mindestens", weil ich nach dem Finden eines Vorkommens nicht weitergesucht habe. Gemerkt habe ich mir den Namen der Kodierung auch nicht. Ich brauchte nur ein Opfer, an dem ich das Verhalten testen konnte. Und da es UTF-8 gibt, sind diese asiatischen Kodierungen für mich unter "historisch" abgelegt (obwohl sie dort immer noch Verwendung finden).

Für Otto-Normal-PHP-Programmierer ist das ganze Geschwafel weniger interessant. Wichtig ist im Prinzip nur,

• die Tabellenfelder mit einer passenden Kodierung zu versehen,
• nach dem Verbindungsaufbau mit SET NAMES eine Kodierung einzustellen,
• und in Statements eingefügte Werte mit mysql_(real_)escape_string() zu bearbeiten.

[*] Alle Stellen zu finden lasse ich mal als Hausaufgabe (besonders für Tom) stehen :-)
Das habe ich befürchtet, habe es aber schon auf dem Zettel... :-)

Als Zusatzaufgabe wäre noch interessant, welche Default-Werte kommen an welchen der Stellen zum Einsatz, wenn keine explizite Angabe erfolgte. Wenn du das auch noch beantworten kannst, solltest du MySQLs Zeichenkodierungskonzept verstanden haben. :-)

echo "$verabschiedung $name";

[Tom]

Hello,

[...] much very importend stuff

Kommt auf den ToDo-Stapel.

Ich muss mich erst mal von Marcos Witz erholen. Der Bauch tut noch vom Lachen weh...

Harzliche Grüße vom Berg
http://bergpost.annerschbarrich.de

Tom

[Martin Hein]

Hallo Tom, hallo Dedlfix,

vorab 1000 Dank für die sehr gut erklärten, für mich dennoch
erschlagenden bg-Infos zum Thema Schriftsätze. Mir ist bewusst,
dass ich mich da nochmal reinsteigern muss, wenn ich gaaaaanz
ausgeglichen bin ;)

Das Problem hatte ich in diesem Projekt zum ersten Mal. Angefangen
hat alles mit einem Metatag in dem 'charset=utf-8' steht. Zum
Problem wurde es, als nachdem ich endlich meine Entwicklung auf den Live-Server übertragen habe, nicht ein Sonderzeichen mehr richtig
dargestellt wurde und sich dann herausstellte, dass der DB-Server
sich grundlegend von dem meiner Entwicklungsumgebung unterschied,
nämlich < MySQL 4.1 war.

Mit euerer Hilfe hab ich das alles so hingebogen bekommen, dass
es funktionierte und ich glaube ich habe zu dem Zeitpunkt sogar,
ein verschwommenes Bild davon gehabt, was da das Thema ist.

Was, wenn ich die Zusammenhänge richtig verstehe, jetzt zum Problem führt, ist folgender Umstand:

In meiner Datenbank (> MySQL 4.1) sind Daten, die aus einem Dump
der Live-Datenbank (< MySQL 4.1) stammen. Ich habe versucht, dass
SQL-Statement, dass zum Fehler führt, mit PHPmyAdmin abzusetzen.
Ich kann mir vorstellen, dass die Verbindung, die PHP mit MySQL
unter meinem PHPMyAdmin herstellt, von UTF8 ausgeht.

Ich werde mal versuchen, das Statement direkt auf dem Liveserver
abzusetzen.

beste gruesse,
martin

[Martin Hein]

... und genau das geht auch ;)

beste gruesse,
martin

[Tom]

Hello,

... und genau das geht auch ;)

Das hieße als Schlussfolgerung jetzt genau WAS?
Ich frage deshalb nach, weil Export/Import mit Releasewechsel nicht sooo häufig hier auftaucht, ich aber auch noch etliche vor mir habe.

Was bedeutet das jetzt für den phpMyAdmin? Kann der mit dem Codierungswechsel nicht umgehen?
Ich setze ihn nicht ein, zumal SQL-Front http://www.sql-front.com/ fast täglich besser wird.
Aber auf den betroffenen Systemen bin ich nachher auf den phpMyAdmin angewiesen...

Harzliche Grüße vom Berg
http://bergpost.annerschbarrich.de

Tom

[dedlfix]

echo $begrüßung;

Was bedeutet das jetzt für den phpMyAdmin? Kann der mit dem Codierungswechsel nicht umgehen?

Du musst einfach nur wissen, welche Kodierung beim Export verwendet wurde, und diese dann beim Import wieder angeben. Das ist alles.

echo "$verabschiedung $name";

[Vinzenz Mai]

Hallo

ich habe in einer MySQL-Tabelle content ein Feld service\_ids,
in dem durch Komma getrennt mehrere Ziffern stehen (z.B. 1,12,5,6').

Das ist keine gute Idee, siehe gern auchhttps://forum.selfhtml.org/?t=162505&m=1057552.
Korrigiere Dein Tabellendesign.

Freundliche Grüße

Vinzenz

[Tom]

Hello Martin,

ich habe in einer MySQL-Tabelle content ein Feld service\_ids,
in dem durch Komma getrennt mehrere Ziffern stehen (z.B. 1,12,5,6').
Das Feld ist vom Typ 'varchar'.

In einer anderen Tabelle service gibt es ein Feld id vom Typ
'int'.

In einem SELECT-Statement würde ich diese beiden Tabellen über
JOIN verknüpfen. Wenn in dem Feld content.service\_ids eine
Ziffer vom Typ int stünde, wäre das ja kein Problem, aber so
weiss ich nicht so genau, wie.

Geht das grundsätzlich ? Und wenn ja dann wie ?

Grundsätzlich geht das schon, drückt aber auf die Bremse...

Es gibt dafür die Funktionen

in()          http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_in
  find_in_set() http://httpd.apache.org/docs/2.0/mod/core.html#directory

Kommt darauf an, wei Du nun verknüpfen willst, welche die richtige für Dich ist.

Immer ganz praktisch:
Funktionsübersicht http://dev.mysql.com/doc/refman/5.1/en/func-op-summary-ref.html

Harzliche Grüße vom Berg
http://bergpost.annerschbarrich.de

Tom

[Martin Hein]

Hallo Tom,

Grundsätzlich geht das schon, drückt aber auf die Bremse...

Die Bremse ist in dem Fall egal. es geht nur um einen Datenauszug,
den ich für den Kunden erstellen will.

Es gibt dafür die Funktionen

in()          http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_in
  find_in_set() http://httpd.apache.org/docs/2.0/mod/core.html#directory

Die Stringfunktionen hatte ich mir auch angesehen ...

Immer ganz praktisch:
Funktionsübersicht http://dev.mysql.com/doc/refman/5.1/en/func-op-summary-ref.html

... und mir vorsgetellt, einfach nach dem Vorkommen eines Teilstrings zu suchen.

Woran das aber scheitert, ist, dass in dem String '1,12,5,6'
der Teilstring '1' vorkommt, in '3,12,5,6' aber auch.

beste gruesse,
martin

Immer ganz praktisch:
Funktionsübersicht http://dev.mysql.com/doc/refman/5.1/en/func-op-summary-ref.html

Harzliche Grüße vom Berg
http://bergpost.annerschbarrich.de

Tom

[Tom]

Hello,

Woran das aber scheitert, ist, dass in dem String '1,12,5,6'
der Teilstring '1' vorkommt, in '3,12,5,6' aber auch.

Dann sind die beiden Funktionen richtig für Dich.
Die berücksichtigen die Trennung der Einzelnen Elemente durch Komma.

Harzliche Grüße vom Berg
http://bergpost.annerschbarrich.de

Tom