Share
Embed
SQLアンチパターン第2版 データベースプログラミングで陥りがちな失敗とその対策 / Intro to SQL Antipatterns 2nd
SQLアンチパターン第2版 データベースプログラミングで陥りがちな失敗とその対策
Developers Summit 2025 Summer
2025年7月18日(金)
https://event.shoeisha.jp/devsumi/20250717/session/5937
Takuto WADA PRO
July 18, 2025
Video
Transcript
63 slides
-
42-Ξϯνύλʔϯୈ൛ 42-Ξϯνύλʔϯୈ൛ +VM !σϒαϛՆ !U@XBEB !UXBEB 📷🙆 🙆 JEUXBEB SFW
5BLVUP8"%" !UXBEB σʔλϕʔεϓϩάϥϛϯάͰؕΓ͕ͪͳࣦഊͱͦͷରࡦ -
ॻ੶ʰ42-Ξϯνύλʔϯୈ൛ʱ w ݄ൃച w ϖʔδʢϖʔδ૿ʣ w ৽ͨʹͭͷষͱͷϛχɾΞϯνύλʔϯ͕Ճ w ࣮ݮͬͨষ͋ΔʢϚδοΫϏʔϯζʣ w
طଘͷষେ෯վగʢมߋՕॴՕॴʣ w ίʔυαϯϓϧ͕1)1͔Β1ZUIPOʹ IUUQTXXXPSFJMMZDPKQCPPLT -
42-͍͍ͨͯɺଞͷݴޠʢ1ZUIPOɺ+BWBɺ$ɺ$ ɺ$ɺ+BWB4DSJQUɺ &MJYJSͳͲʣͰ։ൃ͞ΕͨΞϓϦέʔγϣϯͱڞʹ༻͞Ε·͢ɻΞϓϦέʔγ ϣϯͰ42-Λ༻͍Δํ๏ʹɺదͳͷͱͦ͏Ͱͳ͍ͷ͕͋Γ·͢ɻ͜ͷ ύʔτͰɺΑ͋͘ΔࣦഊྫΛհ͠·͢ɻ ୈ*7෦ɹΞϓϦέʔγϣϯ։ൃͷΞϯνύλʔϯ ষɹϦʔμϒϧύεϫʔυʢಡΈऔΓՄೳύεϫʔυʣ ষɹ42-ΠϯδΣΫγϣϯ ষɹγϡʔυΩʔɾχʔτϑϦʔΫʢٙࣅΩʔܿบʣ ষɹγʔɾϊʔɾΤϏϧʢष͍ͷʹ֖ʣ
ষɹσΟϓϩϚςΟοΫɾΠϛϡχςΟʢ֎ަಛݖʣ ষɹελϯμʔυɾΦϖϨʔςΟϯάɾϓϩγʔδϟʢ͞ͼ͍ͭͨ։ൃඪ४ʣ 新規書き下ろしの章 -
ୈ*෦ ୈ**෦ ୈ***෦ ୈ*7෦ ୈ7෦ " σʔλϕʔεཧઃܭͷΞϯνύλʔϯ σʔλϕʔεཧઃܭͷΞϯνύλʔϯ ΫΤϦͷΞϯνύλʔϯ ΞϓϦέʔγϣϯ։ൃͷΞϯνύλʔϯ
Ϙʔφεɿ֎෦ΩʔͷϛχɾΞϯνύλʔϯ ਖ਼نԽͷϧʔϧ ෦ ෦ߏ -
ɹ$47ྻΛෳͷߦʹׂ͢Δ ɹʮࢲͷίϯϐϡʔλʔͰಈ࡞͍ͯ͠Δͷʹʯ ɹ#*(*/5ेʹେ͖͍ʁ ɹՁ֨ͷอଘ ɹ༧ޠ ɹͯ͢ͷྻʹΠϯσοΫεΛ࡞͢Δ ɹ/05*/ /6-- ɹϙʔλϒϧ42-
ɹΫΤϦͰϥϯμϜʹෳߦΛऔಘ͢Δ ɹϋογϡจࣈྻΛ7"3$)"3ܕͰ֨ೲ͢Δ ɹҾ༻ූͷΫΤϦύϥϝʔλ ɹάϧʔϓ͝ͱͷࣗಈΠϯΫϦϝϯτ ɹߏจΤϥʔϝοηʔδղಡͷ͢͢Ί ɹ໊લͷมߋ ɹ.Z42-ͷετΞυϓϩγʔδϟ ֤ষͷϛχɾΞϯνύλʔϯʢશͯ৽نॻ͖ԼΖ͠ʣ -
CREATE TABLE Products ( product_id SERIAL PRIMARY KEY, product_name VARCHAR(1000),
account_id BIGINT UNSIGNED, -- 他の列. . . FOREIGN KEY (account_id) REFERENCES Accounts(account_id) ); INSERT INTO Products (product_id, product_name, account_id) VALUES (DEFAULT, 'Visual TurboBuilder', 12); มߋલͷςʔ ϒϧఆٛ -
ΞϯνύλʔϯɿΧϯϚ۠ΓϑΥʔϚοτͷϦετΛ֨ೲ͢Δ CREATE TABLE Products ( product_id SERIAL PRIMARY KEY, product_name
VARCHAR(1000), account_id VARCHAR(100), -- カンマ区切りのリスト -- 他の列 . . . ); INSERT INTO Products (product_id, product_name, account_id) VALUES (DEFAULT, 'Visual TurboBuilder', '12,34'); カンマ区切りで 多対多関連を格納してしまう # " % -
ಛఆͷʹؔ࿈͢ΔΞΧϯτͷݕࡧ SELECT * FROM Products AS p JOIN Accounts AS
a ON p.account_id REGEXP '\\b' || a.account_id || '\\b' WHERE p.product_id = 123; JOINに正規表現を使わなければならない…… # " % -
ूΫΤϦͷ࡞ SELECT product_id, LENGTH(account_id) - LENGTH(REPLACE(account_id, ',', '')) + 1
AS contacts_per_product FROM Products; カンマ区切りの文字列の長さからカンマ以外の文字列 の長さを引いたものを計算するような、本来は不要な トリックを使わなければならない # " % -
ಛఆͷʹؔ࿈͢ΔΞΧϯτͷߋ৽ product_id_to_search = 2 value_to_remove = '34' query = "SELECT
product_id, account_id FROM Products WHERE product_id = %s" cursor.execute(query, (product_id_to_search,)) for (row) in cursor: (product_id, account_ids) = row account_id_list = account_ids.split(",") account_id_list.remove(value_to_remove) account_ids = ",".join(account_id_list) query = "UPDATE Products SET account_id = %s WHERE product_id = %s" cursor.execute(query, (account_ids, product_id,)) IDをリストから削除するには、 SQLを2つ発行しなければならない。 古いリストの取得と、更新したリストの保存 # " % -
ΞΧϯτ*%ͷଥੑݕূ INSERT INTO Products (product_id, product_name, account_id) VALUES (DEFAULT, 'Visual
TurboBuilder', '12,34,banana'); アカウントIDではない不正な文字列も入ってしまう # " % -
Ϧετͷ͞ͷ੍ݶ UPDATE Products SET account_id = '10,14,18,22,26,30,34,38,42,46' WHERE product_id =
123; UPDATE Products SET account_id = '101418,222630,343842,467790' WHERE product_id = 123; アカウントIDの長さ次第で 関連づけられる数が左右される アカウントIDの長さ次第で 関連付けられる数が左右される # " % -
CREATE TABLE Contacts ( product_id BIGINT UNSIGNED NOT NULL, account_id
BIGINT UNSIGNED NOT NULL, PRIMARY KEY (product_id, account_id), FOREIGN KEY (product_id) REFERENCES Products(product_id), FOREIGN KEY (account_id) REFERENCES Accounts(account_id) ); ղܾࡦɿަࠩςʔ ϒϧΛ࡞͢Δ -
SELECT p.* FROM Products AS p JOIN Contacts AS c
ON (p.product_id = c.product_id) WHERE c.account_id = 34; ಛఆͷΞΧϯτʹؔ࿈͢Δͷݕࡧʗಛఆͷʹؔ࿈͢ΔΞΧϯτͷݕࡧ SELECT a.* FROM Accounts AS a JOIN Contacts AS c ON (a.account_id = c.account_id) WHERE c.product_id = 123; 正規表現はもう不要で、 インデックスも活用できる 正規表現はもう不要で、 インデックスも活用できる -
SELECT product_id, COUNT(*) AS accounts_per_product FROM Contacts GROUP BY product_id;
ूΫΤϦͷ࡞ SELECT account_id, COUNT(*) AS products_per_account FROM Contacts GROUP BY account_id; シンプルに集約関数を使うだけでいい。 カンマ区切りの文字列の長さからカンマ以外の文字列 の長さを引いたものを計算するようなトリックは不要 シンプルに集約関数を使うだけでいい。 カンマ区切りの文字列の長さからカンマ以外の文字列の 長さを引いたものを計算するようなトリックはもう不要 -
ಛఆͷʹؔ࿈͢ΔΞΧϯτͷߋ৽ INSERT INTO Contacts (product_id, account_id) VALUES (456, 34); DELETE
FROM Contacts WHERE product_id = 456 AND account_id = 34; 関連付けの更新は 交差テーブルへの INSERT や DELETE 関連付けの更新は 交差テーブルへ INSERT や DELETE するだけ -
ϛχɾΞϯνύλʔϯɿ$47ྻΛෳͷߦʹׂ͢Δ WITH RECURSIVE cte AS ( SELECT product_id, product_name, SUBSTRING_INDEX(account_id,
',', 1) AS account_id, SUBSTRING(account_id, LENGTH(SUBSTRING_INDEX(account_id, ',', 1))+2) AS remainder FROM Products UNION ALL SELECT product_id, product_name, SUBSTRING_INDEX(remainder, ',', 1), SUBSTRING(remainder, LENGTH(SUBSTRING_INDEX(remainder, ',', 1))+2) FROM cte WHERE LENGTH(remainder) > 0 ) SELECT product_id, product_name, account_id FROM cte; カンマ区切りの列を再帰SQLで 複数の行に見せかけるトリック # " %