W jaki sposób obsługiwane są klucze auto_increment w INSERT (WYBIERZ * Z…)

Mam table1i table2w MySQL. Oba mają auto_incrementklucz podstawowy id.

Jeśli schematy tabel są zgodne, a ja robię, INSERT INTO table1 (SELECT * FROM table2)co się stanie w odniesieniu do nowych wierszy wstawionych do table1? Czy zachowują swoje stare idwartości i generują konflikty, gdy wiersz table1ma to samo id? Czy nowe wartości są generowane przez auto_increment? Czy to zależy od silnika pamięci lub blokady?

mysql primary-key auto-increment

— Thomas Johnson
źródło

Odpowiedzi:

Możesz wstawić do kolumny automatycznego przyrostu i podać wartość. Jest okej; po prostu zastępuje generator auto-przyrostowy.

Próba wprowadzenia wartości NULL lub 0 lub DEFAULTpominięcie kolumny automatycznego przyrostu w kolumnach instrukcji INSERT powoduje uaktywnienie generatora automatycznego przyrostu.

Więc dobrze jest INSERT INTO table1 SELECT * FROM table2(nawiasem mówiąc, nie potrzebujesz nawiasów). Oznacza to, że wartości id table2zostaną skopiowane dosłownie i nietable1 będą generować nowych wartości.

Jeśli chcesz table1 wygenerować nowe wartości, nie możesz tego zrobić SELECT *. Możesz użyć null lub 0 dla kolumny id:

INSERT INTO table1 SELECT 0, col1, col2, col3, ... FROM table2;

Albo pominiesz kolumnę z listy kolumn instrukcji INSERT i listy wyboru instrukcji SELECT:

-- No id in either case:
INSERT INTO table1 (col1, col2, col3) SELECT col1, col2, col3, ... FROM table2;

Zanim zapytasz, w SQL nie ma składni dla „select * oprócz jednej kolumny”. Musisz przeliterować pełną listę nazw kolumn, które chcesz wstawić.

— Bill Karwin
źródło

Czy nie ma opcji, która pozwala / nie zezwala na wstawienie 0 jako wartości? (W odniesieniu do drugiego akapitu)

— Sascha Rambeaud

Tak, SQL_MODE = NO_AUTO_VALUE_ON_ZERO , w przeciwnym razie nigdy nie moglibyśmy wstawić dosłownej wartości zerowej do kolumny AI. Jeśli używasz tego trybu SQL, NULL nadal działa, aby aktywować generator AI lub pominięcie kolumny również działa.

— Bill Karwin,

@BillKarwin, Jeśli chodzi o twój ostatni akapit, po uzyskaniu wyniku select * from table, czy nie ma sposobu na operację na tym wyniku, aby usunąć jego pierwszą kolumnę?

— Pacerier

@Pacerier, jeśli dobrze rozumiem, pytasz, czy select *może to oznaczać select * except for the first column. Odpowiedź brzmi nie. select *zawsze żąda wszystkich kolumn z tej tabeli. Jeśli chcesz mieć podzbiór, musisz przeliterować kolumny, które chcesz.

— Bill Karwin

@ BillKarwin, Hmm, myślałem o obróceniu stołu, aby zrobić pionowe klauzule, a następnie o odwróceniu go np. A select*from transpose(select*from(transpose(select*from table where Id=1))where col_name<>Id)może bardziej zwięźle select*from table where Id=1 and $col<>Id, jak myślisz?

— Pacerier

Identyfikator z zaznaczenia będzie tą samą wartością wstawioną do tabeli. Spowoduje to błąd, jeśli próbujesz powielić istniejące wiersze.

Bill Karwin: Zanim zapytasz, w SQL nie ma składni dla „select * oprócz jednej kolumny”.

Można to osiągnąć dzięki pewnej kreatywności:

SET @sql = CONCAT('INSERT INTO <table> SELECT null, 
    ', (SELECT GROUP_CONCAT(COLUMN_NAME) 
    FROM information_schema.columns 
    WHERE table_schema = '<database>' 
    AND table_name = '<table>' 
    AND column_name NOT IN ('id')), ' 
from <table> WHERE id = <id>');  

PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

Spowoduje to, że nowy wiersz otrzyma automatycznie przyrostowy identyfikator zamiast identyfikatora z wybranego wiersza.

— curmil
źródło

Sprytne, ale dla mnie liczy się to jako przeliterowanie pełnej listy nazw kolumn, które chcesz wstawić.

— Bill Karwin

Cóż, mysql automatycznie pisze to w porównaniu do robienia tego ręcznie. Jeśli masz tabele z dużą liczbą kolumn, zapobiegnie to pominięciu jednej lub błędnej pisowni kilku.

— curmil