MYSQL: NULL != ''

[Matthias Apsel]

Hallo alle,

Ich lege eine Tabelle an

CREATE TABLE Dokument (
   D_Name VARCHAR(100) NOT NULL -- und noch weitere Spalten
)

mit dem Ziel leere Namen zu vermeiden. Dennoch kann ich die leere Zeichenkette in die Spalte eintragen.

SELECT * FROM `Dokument` WHERE `D_Name` = '' -- liefert Ergebnisse
SELECT * FROM `Dokument` WHERE `D_Name` IS NULL -- liefert ein leeres Resultat

Offensichtlich ist '' != NULL. Wie kann ich auf SQL-Ebene das Eintragen leerer Werte unterbinden?

Bis demnächst
Matthias

[ursus contionabundo]

Vielleicht so:

http://www.mysqltutorial.org/mysql-check-constraint/

CREATE TABLE Dokument (
   D_Name VARCHAR(100) NOT NULL  CHECK(D_Name > "") …,
   …
)

[ursus contionabundo]

Wie kann ich auf SQL-Ebene das Eintragen leerer Werte unterbinden?

Vielleicht so:

http://www.mysqltutorial.org/mysql-check-constraint/

Mögliche Lösung mit View:

CREATE TABLE Dokument ( D_Name VARCHAR(100) NOT NULL
Query OK, 0 rows affected (0,01 sec)

CREATE VIEW DokInsert AS SELECT D_Name from Dokument WHERE D_NAME > "" WITH CHECK OPTION;
Query OK, 0 rows affected (0,02 sec)

insert into Dokument set D_NAME=""; ### SO NICHT: UMGEHT CHECK ###
Query OK, 1 row affected (0,01 sec

insert into DokInsert set D_NAME="";
ERROR 1369 (HY000): CHECK OPTION failed 'test.DokInsert'

insert into DokInsert (D_NAME) values ("");
ERROR 1369 (HY000): CHECK OPTION failed 'test.DokInsert'

insert into DokInsert set D_NAME="foo";
Query OK, 1 row affected (0,01 sec)

update DokInsert set D_NAME="" where D_NAME="foo";
ERROR 1369 (HY000): CHECK OPTION failed 'test.DokInsert'

select version();
+-----------+
| version() |
+-----------+
| 5.7.20    |
+-----------+
1 row in set (0,00 sec)

[pl]

Leere Zeichenketten sind ja auch nicht NULL sondern klar definierte Strings. Tipp: Definiere immer einen default beim Anlegen von Tabellen. GG

[pl]

geht so:

$dbh->do("drop table stings");
$dbh->do(q(
    create table stings(
        sting varchar(123) not null default "---",
        name  varchar(123) not null default '---'
    )
)) or die $@;

$dbh->do(q(insert into stings (name) values(?)),{},"otto");

__END__
mysql> select * from stings;
+-------+------+
| sting | name |
+-------+------+
| ---   | otto |
+-------+------+
1 row in set (0.00 sec)

GG

[dedlfix]

Tach!

    create table stings(
        sting varchar(123) not null default "---",
        name  varchar(123) not null default '---'
    )

Es ging darum, das Eintragen von Leerstrings zu verhindern. Default-Werte ziehen aber nur, wenn man NULL übergibt oder die Spalte im INSERT nicht mit angibt. Das Eintragen-Können von Leerstrings wird so nicht verhindert.

dedlfix.

[Felix Riesterer]

Lieber Matthias,

Offensichtlich ist '' != NULL.

richtig. War das noch nicht klar?

Wie kann ich auf SQL-Ebene das Eintragen leerer Werte unterbinden?

Mir nicht bekannt, ich löse das auf PHP-Ebene, indem ich leere Werte zu null konvertiere und erst dann in den PDO-Aufrufen zu SQL umwandle. Deshalb verwende ich lieber kein NOT NULL in solchen Fällen.

Liebe Grüße,

Felix Riesterer.

[Matthias Apsel]

Hallo Felix Riesterer,

Offensichtlich ist '' != NULL.

richtig. War das noch nicht klar?

bis eben noch nicht.

Wie kann ich auf SQL-Ebene das Eintragen leerer Werte unterbinden?

Mir nicht bekannt, ich löse das auf PHP-Ebene, indem ich leere Werte zu null konvertiere und erst dann in den PDO-Aufrufen zu SQL umwandle. Deshalb verwende ich lieber kein NOT NULL in solchen Fällen.

Ja, das habe ich auch als Lösung im Sinn.

Bis demnächst
Matthias

[pl]

Mir nicht bekannt, ich löse das auf PHP-Ebene, indem ich leere Werte zu null konvertiere und erst dann in den PDO-Aufrufen zu SQL umwandle. Deshalb verwende ich lieber kein NOT NULL in solchen Fällen.

Not null kannst da aber auch und gerade in diesem Fall setzen. Voraussetzung ist ein default

MFG

[Felix Riesterer]

Lieber pl,

Not null kannst da aber auch und gerade in diesem Fall setzen. Voraussetzung ist ein default

Du hast in dieser Diskussion hier anscheinend weder mitgelesen noch mitgedacht, sonst hättest Du inzwischen dazugelernt, dass Dein Vorschlag inhaltlich völlig falsch ist.

Liebe Grüße,

Felix Riesterer.

[pl]

Not null kannst da aber auch und gerade in diesem Fall setzen. Voraussetzung ist ein default

Du hast in dieser Diskussion hier anscheinend weder mitgelesen noch mitgedacht, sonst hättest Du inzwischen dazugelernt, dass Dein Vorschlag inhaltlich völlig falsch ist.

Mein Vorschlag unterstützt und ergänzt Deinen Vorschlag! Denn Du selbst hast ja eine Lösung auf PHP Ebene vorgeschlagen!

[Felix Riesterer]

Lieber pl,

Mein Vorschlag unterstützt und ergänzt Deinen Vorschlag! Denn Du selbst hast ja eine Lösung auf PHP Ebene vorgeschlagen!

nein, das tut Dein Vorschlag nicht. Ein Default-Wert hat nur in ganz bestimmten Fällen seinen Sinn, nämlich dann, wenn für dieses Feld kein Wert (auch kein leerer) angegeben wurde. Das ist aber nicht das vom OP beschriebene Szenario!

Liebe Grüße,

Felix Riesterer.

[pl]

Wissen Sie Herr Riesterer, ich mache hier nur Vorschläge aufgrund meiner langjährigen Erfahrung mit Datenbankanwendungen. Und die ist, daß ein default in den meisten Fällen sinnvoll ist. Und in diesem Fall ist ein default ganz besonders sinnvoll. Im Übrigen unterstütze ich ja Ihren Vorschlag. GGA

[ursus contionabundo]

@PL Hm. Du hast beim Scrollen die Aufgabenstellung aus den Blick verloren:

• Dennoch kann ich die leere Zeichenkette in die Spalte eintragen.
• Offensichtlich ist '' != NULL.
• Wie kann ich auf SQL-Ebene das Eintragen leerer Werte unterbinden?

Bei Deinem Vorschlag (Erst einen Default in der Datenbank definieren und dann leere Strings durch NULL ersetzen) wird aber ETWAS eingetragen. Dein Vorschlag kann als Ergänzung für einen anderen Leser mit alternierendem Programmablaufplan sinnvoll sein - @Matthias Apsel wollte das (dem Wortlaut nach) aber gerade nicht. Wobei natürlich die Frage im Raum steht, warum er meinen (funktionierenden und getesteten) Lösungsvorschlag nicht kommentiert hat. Was ja immer den Gedanke befördert, dass er ihn ignoriert. Was dann wieder den Gedanke daran nach sich zieht, dass das auch nicht die gesuchte Lösung ist. Was dann die Idee nach sich zieht, dass er gar nicht will, was er als sein Wollen beschrieben hat.

[pl]

Jeder Vorschlag ist sinnvoll!

warum er meinen (funktionierenden und getesteten) Lösungsvorschlag nicht kommentiert hat. Was ja immer den Gedanke befördert, dass er ihn ignoriert.

Kann er ja machen. Besser wärs natülich wenn er einfach mal danke sagen würde!

[ursus contionabundo]

Abgesehenen davon, dass mir unklar ist, warum er den Test (String == "") teuer mit einem Datenbankrequest machen will. Ich würde das stets in PHP machen und die Datenbank nur bemühen wenn es sein muss. Erstens wegen der Laufzeit und zweitens weil die Anwendung ohne "Spezialkram" vielleicht auch mal mit sqlite läuft.

[pl]

Abgesehenen davon, dass mir unklar ist, warum er den Test (String == "") teuer mit einem Datenbankrequest machen will. Ich würde das stets in PHP machen und die Datenbank nur bemühen wenn es sein muss. Erstens wegen der Laufzeit und zweitens weil die Anwendung ohne "Spezialkram" vielleicht auch mal mit sqlite läuft.

Genau! Und: .. not null default 'mydef' auf diesen Fall praktisch angewandt heißt, daß man in PHP dieses Feld einfach außen vor lässt wenn der Fall Leerstring eintritt. Somit wird der default genommen, es sei denn man verzichtet komplett auf das gesamte insert Statement.

Und genau das ist die eigentliche Frage: Was soll gemacht werden wenn bei der Datenerhebung ein Leerstring auftritt!? Wenn es darum geht ein insert nicht zu tun fällt das ohnehin nicht in den Verantwortungsbereich des RDBMS. Und wenn man andererseits bei einem insert den Leerstring nicht haben will setzt man einen davon abweichenden default. MFG

[Matthias Apsel]

Hallo ursus contionabundo,

Wobei natürlich die Frage im Raum steht, warum er meinen (funktionierenden und getesteten) Lösungsvorschlag nicht kommentiert hat. Was ja immer den Gedanke befördert, dass er ihn ignoriert.

Das tut er. Weil er dem Vorschlag von @Felix Riesterer folgt.

Bis demnächst
Matthias

[ursus contionabundo]

Vorschlag von @Felix Riesterer

leere Werte zu null konvertiere und erst dann in den PDO-Aufrufen zu SQL umwandle.

Hm. Der läuft also auf folgendes hinaus:

Erst in PHP den Wert "" zu NULL konvertieren, dann in SQL einbauen, dann die Datenbank fragen, ob die den Eintrag akzeptiert. Falls nicht sodann mit oder wie bei einer Exception reagieren.

Alternative:

In PHP den Wert "" feststellen und die Datenbank gar nicht erst fragen sondern gleich darauf mit oder wie bei einer Exception zu regieren klingt irgendwie direkter und also "billiger" - oder?

[Christian Kruse]

Hallo Matthias,

Wobei natürlich die Frage im Raum steht, warum er meinen (funktionierenden und getesteten) Lösungsvorschlag nicht kommentiert hat. Was ja immer den Gedanke befördert, dass er ihn ignoriert.

Das tut er. Weil er dem Vorschlag von @Felix Riesterer folgt.

Letztlich kannst nur du wissen, was deine genaue Anforderung ist, deshalb kann ich das hier nur als allgemeinen Rat formulieren: man sollte die Integrität der Daten nie auf Applikations-Ebene sicherstellen, sondern immer auf Datenbankebene. Sonst kommt man früher oder später immer auf das Problem, dass ein Zugriff an dem spezifischen Code-Pfad vorbei etwas einträgt, was nicht vorgesehen war.

LG,
CK

[ursus contionabundo]

Jepp. (Auch der Vorschlag von Felix ("" → NULL in PHP) verschiebt die Integritätsprüfung in die Applikation.)

Sonst kommt man früher oder später immer auf das Problem, dass ein Zugriff an dem spezifischen Code-Pfad vorbei etwas einträgt, was nicht vorgesehen war.

Ich will nicht verschweigen, dass auch bei meiner Lösung ein Eintrag in die Tabelle ohne den Umweg über die View das Eintragen von "" ermöglicht.

Ich teste gerade mal

… check (spalte not like '')

[ursus contionabundo]

check tuts nicht. Aber die Lösung mit Procedur und Trigger tut es.

• Hat den Vorteil, dass die View nicht umgangen werden kann.
• Hat den Vorteil. dass die Prozedur leicht um weitere Fehlerprüfungen und individuelle Fehlernummern ergänzt werden kann.

CREATE TABLE Dokument ( D_Name VARCHAR(100) NOT NULL);
# Query OK, 0 rows affected (0,02 sec)
 
DELIMITER $
  
CREATE PROCEDURE `check_Dokument`(IN D_Name varchar( 100 ) )
    BEGIN
        IF D_NAME LIKE "" THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'D_NAME was empty';
        END IF;
    END$
# Query OK, 0 rows affected (0,02 sec)

 
CREATE TRIGGER `Dokument_before_insert` BEFORE INSERT ON `Dokument`
    FOR EACH ROW
    BEGIN
        CALL check_Dokument(new.D_Name);
    END$   
# Query OK, 0 rows affected (0,02 sec)

CREATE TRIGGER `parts_before_update` BEFORE UPDATE ON `Dokument`
    FOR EACH ROW
    BEGIN
        CALL check_Dokument(new.D_Name);
    END$   
# Query OK, 0 rows affected (0,00 sec)

DELIMITER ;
 
INSERT INTO Dokument (D_Name) values ("foo");
# Query OK, 1 row affected (0,01 sec)

INSERT INTO Dokument (D_Name) values ("");
# ERROR 1644 (45000): D_NAME was empty

[Matthias Apsel]

Hallo ursus contionabundo,

Das sieht ebenfalls sehr spannend aus und erweitert auf jeden Fall meinen Horizont.

Bis demnächst
Matthias

[1unitedpower]

check tuts nicht.

Das könnte an deinem SQL-Server liegen. MySQL unterstützt check-constraints erst ab Version 8.0.16, die noch nicht offiziell veröffentlicht wurde. MariaDB untersützt check-constraints seit Version 10.2.1 (seit fast drei Jahren).

[ursus contionabundo]

Das könnte an deinem SQL-Server liegen.

Ja. Mein Tisch gibt die Form meiner Stirn schon wieder ... Ich hatte tatsächlich einen alten MySql-5.7-Server (Eine virtuelle Maschine, gemacht für ein Seminar) am Start.

Ich hab die Geschichte um einen weiteren Fehler und um einen regulären Ausdruck erweitert. Geht mit Mysql 5.7 und Mariadb 10.0:

CREATE TABLE `tabelle` ( `spalte` VARCHAR( 100 ) NOT NULL );

DELIMITER $
  
CREATE PROCEDURE `check_tabelle`(IN `spalte` varchar( 100 ) )
    BEGIN
        IF `spalte` LIKE "" THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'spalte was empty';
        END IF;
        
        SET @test = `spalte` REGEXP '[A-Za-z]{4}';
        IF NOT @test THEN
            SIGNAL SQLSTATE '45001'
            SET MESSAGE_TEXT = 'Spalte was to short (Minimum: 4 chars [A-Za-z])';
        END IF;        
    END
$
 
CREATE TRIGGER `tabelle_before_insert` BEFORE INSERT ON `tabelle`
    FOR EACH ROW
    BEGIN
        CALL check_tabelle(new.spalte);
    END
$   

CREATE TRIGGER `tabelle_before_update` BEFORE UPDATE ON `tabelle`
    FOR EACH ROW
    BEGIN
        CALL check_tabelle(new.spalte);
    END
$   

DELIMITER ;

INSERT INTO `tabelle` ( spalte ) values ( "foobar" );
INSERT INTO `tabelle` ( spalte ) values ( "foo" );
INSERT INTO `tabelle` ( spalte ) values ( "" );

"foobar" : Query OK, 1 row affected (0,00 sec)
"foo"    : ERROR 1644 (45001): Spalte was to short (Minimum: 4 chars [A-Za-z])
""       : ERROR 1644 (45000): spalte was empty

Das werde ich jetzt mal sorgfältig als Beispiel verwahren…

[MudGuard]

Hi,

    IF `spalte` LIKE "" THEN

warum LIKE für einen Vergleich mit einem konstanten Wert?

cu,
Andreas a/k/a MudGuard

[ursus contionabundo]

    IF `spalte` LIKE "" THEN

warum LIKE für einen Vergleich mit einem konstanten Wert?

Ich kann Dir zwei Dinge sagen:

1. Du kannst das obige an Deine Bedürfnisse und Deine Fähigkeiten anpassen.
2. Mit der Ansicht, dass die Meinung meiner Mutter auch nur irgendwie maßgeblich sei, war ich fertig, als diese meine Leistung in einer Mathearbeit (mit einem glatten Einser und allen Zusatzpunkten) würdigte indem sie sich über einen beschissenen Rechtschreibfehler in einem Antwortsatz aufregte.

[Rolf B]

Hallo Jörg,

das ist nicht mein Kreuz Minus.

Wenn jemand eine clevere Lösung mit Prozeduren und Triggern präsentiert, und darin ein Test auf Gleichheit mit "LIKE" gemacht wird, dann unterstellt man doch erst einmal, dass auch dieses LIKE eine clevere Bedeutung hat und etwas tut, das vom = Operator nicht geleistet wird. Und die Rückfrage, worin diese Cleverness besteht - wenn man sie selbst nicht sofort sieht - ist schon berechtigt.

Als Reaktion präsentierst Du eine wortreiche Version von „f*** dich“. Müssen wir daraus schließen, dass das LIKE durch ein = ersetzt werden kann und Du Dich ärgerst, dass Dir das nicht selbst aufgefallen ist?

Vielleicht nicht. Ich habe in die Doku geschaut und kurz was ausprobiert - und ja, es kann einen Grund für LIKE geben. Der = Operator ignoriert abschließende Leerstellen, demzufolge ist " " = "" TRUE. Der LIKE Operator tut das nicht, " " LIKE "" liefert FALSE^[1].

D.h. man muss nun entscheiden, ob man einen String mit Spaces darin als gültig akzeptieren will oder nicht, und abhängig davon den = oder LIKE Operator einsetzen.

Rolf

---

1. Für einen gewissen Wert von TRUE (1) und FALSE (0). ↩︎

[Self-Nachtwächter]

Als Reaktion präsentierst Du eine wortreiche Version von „f*** dich“.

Ja, ich habe auf Krittelei reagiert. Das "warum LIKE für einen Vergleich mit einem konstanten Wert?" ist mit allem Verlaub einfach nur „gründlich missglückte Besserwisserei“. Dazu unten mehr.

Müssen wir daraus schließen, dass das LIKE durch ein = ersetzt werden kann und Du Dich ärgerst, dass Dir das nicht selbst aufgefallen ist?

Wieso sollte LIKE hier durch = ersetzbar sein? Aus dem besprochenen Quelltext:

        IF D_NAME LIKE "" THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'D_NAME was empty';
        END IF;

Zitat: 'D_NAME was empty'

LIKE kann also NICHT durch ein = ersetzt werden ohne auch den MESSAGE_TEXT zu ersetzen. "12.5.1 String Comparison Functions" ist im Handbuch frei zugänglich. Insoweit habe ich mich erst einmal streng an die ursprüngliche Aufgabe "Verhindern des Eintrags eines leeren Strings" gehalten und für alles (selbst mit trüber Glaskugel) erwartbare den Tipp mit dem Regex nachgereicht.

[Rolf B]

Hallo Self-Nachtwächter,

„gründlich missglückte Besserwisserei“

Oder eine Nachfrage wegen einer Wissenslücke. Ich musste ja auch erstmal nachlesen und war verblüfft, dass es diesen Unterschied gibt.

Das gehört sicherlich zu den unbekannteren Eigenschaften von SQL und bin über diese Frage eigentlich ganz froh, ich habe etwas dadurch gelernt. Kurze Umfrage unter Kollegen ergab, dass ich damit nicht allein war. Eine Rückfrage war "LIKE ohne Pattern? Gibt das keinen Fehler?"...

Rolf

[ursus contionabundo]

Ja. Mein Tisch gibt die Form meiner Stirn schon wieder ... Ich hatte tatsächlich einen alten MySql-5.7-Server

Hab's "grad" gesehen. Der "alte MySql-5.7-Server" ist immer noch der, der auch von Ubuntu 18.04 installiert wird, wenn man das Repo nicht wechselt.

[encoder]

man sollte die Integrität der Daten nie auf Applikations-Ebene sicherstellen, sondern immer auf Datenbankebene.

Ich würde beides tun. Etwas das der Nutzer falsch machen kann und wird sollte nicht bis in die Datenbank gelangen, dort eine für den Nutzer unverständliche Fehlermeldung erzeugen und eine Transaktion zurücksetzen müssen.

[Christian Kruse]

Hallo encoder,

man sollte die Integrität der Daten nie auf Applikations-Ebene sicherstellen, sondern immer auf Datenbankebene.

Ich würde beides tun.

Ich habe bewusst das Wort „sicherstellen“ hier gewählt, weil es mir darum ging, dass man die Integrität des Datenmodells nicht in der Anwendung erzwingen sollte.

Dass man in der Anwendung zusätzlich (in gewissen Maßen - nicht jede Prüfung kann man auf Client-Seite erledigen) prüft, ob die Eingaben wie erwartet sind, hielt ich für selbstverständlich 😉

LG,
CK

[Tabellenkalk]

Hallo,

Wie kann ich auf SQL-Ebene das Eintragen leerer Werte unterbinden?

Darf es denn in dem Feld doppelte Einträge geben? Wenn nicht, könnte 'default' plus 'unique' zielführend sein.

Gruß
Kalk