Ograniczenie CHECK w MySQL nie działa


126

Najpierw stworzyłem taki stół

CREATE TABLE Customer (
  SD integer CHECK (SD > 0),
  Last_Name varchar (30),
  First_Name varchar(30)
);

a następnie wstawił wartości do tej tabeli

INSERT INTO Customer values ('-2','abc','zz');

MySQL nie pokazuje błędu, zaakceptował wartości.


Częściowo się zgadzam. Biorąc pod uwagę, że próbowałeś go użyć, można założyć, że zadawałeś oba pytania. W rzeczywistości odpowiedź, którą zaakceptowałeś, głównie wyjaśnia, dlaczego to nie działa.
igorrs

1
Możesz głosować na tę prośbę o funkcję: bugs.mysql.com/bug.php?id=3464, ale nie otrzymała ona żadnej uwagi od dziesięciu lat.
Jared Beck

11
Możesz używać ograniczeń CHECK w MariaDB od wersji 10.2.1 .
joanq

Odpowiedzi:


140

MySQL 8.0.16 to pierwsza wersja obsługująca ograniczenia CHECK.

Przeczytaj https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html

Jeśli używasz MySQL 8.0.15 lub starszego, w Podręczniku MySQL jest napisane:

CHECKKlauzula jest analizowany, ale ignorowane przez wszystkich silników składowania.

Wypróbuj spust ...

mysql> delimiter //
mysql> CREATE TRIGGER trig_sd_check BEFORE INSERT ON Customer 
    -> FOR EACH ROW 
    -> BEGIN 
    -> IF NEW.SD<0 THEN 
    -> SET NEW.SD=0; 
    -> END IF; 
    -> END
    -> //
mysql> delimiter ;

Mam nadzieję, że to pomoże.


9
Tutaj dowiesz się, jak zamiast tego wywołać błąd: stackoverflow.com/a/7189396/1144966
petermeissner

41
To jeden z wielu błyszczących powodów, dla których zawsze będę używać PostgreSQL zamiast MySQL, mając jakikolwiek wybór.
Reinderien

5
Zastanawiam się, czy programowanie w MySQL zajęłoby 10 minut, czy 15 minut, aby wyświetlić ostrzeżenie, jeśli parser napotka CHECKzdefiniowane ograniczenie. Ach, to byłoby zbyt proste ...
gaborsch,

75

Niestety MySQL nie obsługuje ograniczeń sprawdzania SQL. Można je zdefiniować w zapytaniu DDL ze względu na zgodność, ale są one po prostu ignorowane.

Jest prosta alternatywa

Można tworzyć BEFORE INSERTi BEFORE UPDATEwyzwalacze, które powodują błąd lub ustawiają wartość domyślną pola, gdy wymagania danych nie są spełnione.

Przykład BEFORE INSERTpracy po MySQL 5.5

DELIMITER $$
CREATE TRIGGER `test_before_insert` BEFORE INSERT ON `Test`
FOR EACH ROW
BEGIN
    IF CHAR_LENGTH( NEW.ID ) < 4 THEN
        SIGNAL SQLSTATE '12345'
            SET MESSAGE_TEXT := 'check constraint on Test.ID failed';
    END IF;
END$$   
DELIMITER ;  

Przed MySQL 5.5 trzeba było spowodować błąd, np. Wywołać niezdefiniowaną procedurę.

W obu przypadkach powoduje to niejawne wycofanie transakcji. MySQL nie zezwala na samą instrukcję ROLLBACK w procedurach i wyzwalaczach.

Jeśli nie chcesz wycofywać transakcji (INSERT / UPDATE powinno przejść nawet przy nieudanym "ograniczeniu sprawdzającym" możesz nadpisać wartość używając SET NEW.ID = NULLktóra ustawi id na wartość domyślną pola, nie ma to sensu dla id Chociaż

Edycja: Usunięto zbłąkany cytat.

W odniesieniu do :=operatora:

W przeciwieństwie do =tego :=operator nigdy nie jest interpretowany jako operator porównania. Oznacza to, że możesz użyć :=w dowolnej prawidłowej instrukcji SQL (nie tylko w instrukcjach SET), aby przypisać wartość do zmiennej.

https://dev.mysql.com/doc/refman/5.6/en/assignment-operators.html

Odnośnie cudzysłowów identyfikatora odwrotnego znaku:

Znakiem cudzysłowu identyfikatora jest lewy przycisk („„ ”)

Jeśli tryb ANSI_QUOTES SQL jest włączony, dozwolone jest również cytowanie identyfikatorów w podwójnych cudzysłowach

http://dev.mysql.com/doc/refman/5.6/en/identifiers.html


7
... niezbyt proste, przynajmniej w porównaniu do CHECK :(. Coupla tutes : net.tutsplus.com/tutorials/databases/… , sitepoint.com/how-to-create-mysql-triggers
Ben

ugh to wygląda na bardzo nieporęczne. Chyba raczej tworzyć krotki w Pythonie i sprawdzić wartości tam zamiast uczynić to w.
OzzyTheGiant

Szybkie pytanie: dlaczego to nie działa bez ustawienia DELIMITER?
ddz

52

CHECK ograniczenia są ignorowane przez MySQL, jak wyjaśniono w niewielkim komentarzu w dokumentacji: CREATE TABLE

CHECKKlauzula jest analizowany, ale ignorowane przez wszystkich silników składowania.


2
@thefiloe: Prawidłowo, w innych DBMS z poprawną implementacją CHECKograniczeń, jeśli wartości CHECKszacowane to FALSEwtedy wstawianie (lub aktualizacja) nie jest wykonywane i powoduje błąd.
ypercubeᵀᴹ

Naprawiono w MariaDB (zobacz tę odpowiedź stackoverflow.com/a/44333349 ).
Jérôme

@ Jérôme Wiem, mam kilka (nowszych) odpowiedzi, które obejmują ulepszenia w tym obszarze (były inne sposoby obejścia tego problemu, zarówno w MariaDB, jak i MySQL, zanim MariaDB poprawnie zaimplementowała ograniczenia CHECK). Nie jestem pewien, czy powinienem edytować wszystkie moje stare odpowiedzi!
ypercubeᵀᴹ

Przypuszczam, że mój komentarz z linkiem do nowszej odpowiedzi jest w porządku. Albo lepiej niż nic. Może powinienem był zredagować. Nie chciałem wywierać na ciebie presji, żeby cokolwiek zrobić.
Jérôme



1

Ograniczenia sprawdzające są obsługiwane od wersji 8.0.15 (jeszcze nie zostanie wydana)

https://bugs.mysql.com/bug.php?id=3464

[23 stycznia, 16:24] Paul Dubois

Wysłane przez programistę: Naprawiono w 8.0.15.

Wcześniej MySQL dopuszczał ograniczoną formę składni ograniczenia CHECK, ale analizował ją i ignorował. MySQL implementuje teraz podstawowe funkcje ograniczeń CHECK tabel i kolumn we wszystkich silnikach pamięci masowej. Ograniczenia są definiowane za pomocą instrukcji CREATE TABLE i ALTER TABLE.


1

Zaktualizuj do MySQL 8.0.16, aby użyć checks:

Począwszy od MySQL 8.0.16 funkcja CREATE TABLE zezwala na podstawowe funkcje ograniczeń CHECK tabel i kolumn dla wszystkich silników pamięci masowej. CREATE TABLE zezwala na następującą składnię ograniczenia CHECK, zarówno dla ograniczeń tabeli, jak i ograniczeń kolumn

Dokumentacja kontroli MySQL


-2

spróbuj z set sql_mode = 'STRICT_TRANS_TABLES'ORSET sql_mode='STRICT_ALL_TABLES'


2
to faktycznie nie pomaga (MySQL 5.6) zapobiega wprowadzaniu danych fałszywego typu, ale nie przed wprowadzaniem danych, które nie spełniają CHECKograniczenia
petermeissner
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.