SQLアンチパターン第2版データベースプログラミングで陥りがちな失敗とその対策 / Intro to SQL Antipatterns 2nd

42-Ξϯνύλʔϯୈ൛ 42-Ξϯνύλʔϯୈ൛ +VM !σϒαϛՆ !U@XBEB !UXBEB 📷🙆 🙆 JEUXBEB SFW
5BLVUP8"%" !UXBEB σʔλϕʔεϓϩάϥϛϯάͰؕΓ͕ͪͳࣦഊͱͦͷରࡦ

Agenda ͸͡Ίʹ ʰ42-Ξϯνύλʔϯୈ൛ʱʹ͍ͭͯ ຊॻͷΞϯνύλʔϯͷ۩ମྫ ͓ΘΓʹ 👉

೥ɺੈք͸7JCF$PEJOHͷԌʹแ·Εͨʂ IUUQTYDPNLBSQBUIZTUBUVT

IUUQTYDPN@QPUFCPZ@TUBUVT %#·ΘΓ͸ઃܭࣦഊͷμϝʔδ͕େ͖͘ɺϦΧόϦ΋೉͍͠

ຊॻ͸%#ઃܭ΍42-هड़ͷࡍʹආ͚Δ΂͖ࣄฑ ΛষͰͭɺݸ঺հ͢Δॻ੶Ͱ͢ɻ ϦϨʔγϣφϧσʔλϕʔεΛத৺ʹਾ͑ͨγε ςϜ։ൃʹ͸ɺ༷ʑͳ৔໘ͰؕΓ΍͍ࣦ͢ഊʢΞ ϯνύλʔϯʣ͕͋Γ·͢ɻ ຊॻ͸σʔλϕʔε࿦ཧઃܭɺσʔλϕʔε෺ཧ ઃܭɺΫΤϦͷهड़ɺΞϓϦέʔγϣϯ։ൃͱ͍ ͏ͭͷΧςΰϦʹ෼͚ɺͦΕͧΕͷ෼໺ʹ͓͚ ΔΞϯνύλʔϯΛ঺հ͠ɺࣦഊΛආ͚ΔͨΊͷ ΑΓྑ͍ํ๏Λ঺հ͠·͢ɻ
ॻ੶ʰ42-Ξϯνύλʔϯʱ IUUQTXXXPSFJMMZDPKQCPPLT

w ೥݄ൃച w ೥ʹ౉Δϕετηϥʔ w ྦྷܭສ෦ಥഁ w ిࢠॻ੶࣮ച໿ ෦ ॻ੶ʰ42-Ξϯνύλʔϯʱ

͜Ε·ͰചΕͨʰ42-ΞϯνύλʔϯʱΛॎʹฒ΂Δͱೆถ࠷ߴๆΞίϯΧάΞʢඪߴ NʣΑΓߴ͍ よくわからないがすごそうエベレスト超えを目指したい

೥ͷ࣌Λܦͯୈ൛͕ग़൛͞Ε·ͨ͠ ೥

ॻ੶ʰ42-Ξϯνύλʔϯୈ൛ʱ w ೥݄ൃച w ϖʔδʢϖʔδ૿ʣ w ৽ͨʹͭͷষͱͷϛχɾΞϯνύλʔϯ͕௥Ճ w ࣮͸ݮͬͨষ΋͋ΔʢϚδοΫϏʔϯζʣ w
طଘͷষ΋େ෯վగʢมߋՕॴ͸໿Օॴʣ w ίʔυαϯϓϧ͕1)1͔Β1ZUIPOʹ IUUQTXXXPSFJMMZDPKQCPPLT

ຊॻ͸ɺ෦ߏ੒ʹͳ͍ͬͯ·͢ɻΞϯνύλʔϯΛҎԼͷͭͷΧςΰϦʔʹ ෼͚ɺ֤ύʔτͰͦΕͧΕΛղઆ͍͖ͯ͠·͢ɻ ຊॻͷߏ੒ σʔλϕʔε࿦ཧઃܭͷΞϯνύλʔϯ σʔλϕʔε෺ཧઃܭͷΞϯνύλʔϯ ΫΤϦͷΞϯνύλʔϯ ΞϓϦέʔγϣϯ։ൃͷΞϯνύλʔϯ ୈ*෦ ୈ**෦ ୈ***෦
ୈ*7෦

ίʔυΛॻ͖࢝ΊΔલʹɺσʔλϕʔεʹ֨ೲ͢Δ৘ใΛݟۃΊɺσʔλͷߏ੒ ΍ؔ࿈෇͚Λ࠷ળͷํ๏Ͱ࣮ݱ͢ΔͨΊͷઃܭΛߦ͏ඞཁ͕͋Γ·͢ɻ͜ͷ࡞ ۀʹ͸ɺσʔλϕʔεͷςʔϒϧ΍ྻɺؔ࿈ʢϦϨʔγϣϯγοϓʣͷઃܭͳͲ ͕͋Γ·͢ɻ ୈ*෦ɹσʔλϕʔε࿦ཧઃܭͷΞϯνύλʔϯ ষɹδΣΠ΢ΥʔΫʢ৴߸ແࢹʣ ষɹφΠʔϒπϦʔʢૉ๿ͳ໦ʣ ষɹ*%ϦΫϫΠΞυʢͱΓ͋͑ͣ*%ʣ ষɹΩʔϨεΤϯτϦʢ֎෦Ωʔݏ͍ʣ ষɹ&"7ʢΤϯςΟςΟɾΞτϦϏϡʔτɾόϦϡʔʣ
ষɹϙϦϞʔϑΟοΫؔ࿈ ষɹϚϧνΧϥϜΞτϦϏϡʔτʢෳ਺ྻଐੑʣ ষɹϝλσʔλτϦϒϧʢϝλσʔλେ૿৩ʣ

֨ೲ͢ΔσʔλΛܾఆͨ͠ޙʹ͸ɺ࢖༻͢Δ3%#.4ͷػೳΛ࠷େݶʹ׆͔͠ ͯɺσʔλ؅ཧͷ࣮૷Λߦ͍·͢ɻ͜ͷ࡞ۀʹ͸ɺςʔϒϧ΍ΠϯσοΫεͷఆ ٛɺσʔλܕͷܾఆͳͲ͕͋Γ·͢ɻఆٛʹ͸42-ͷ$3&"5&5"#-&εςʔτ ϝϯτͷΑ͏ͳσʔλఆٛݴޠʢ%%-ɿ%BUB%F fi OJUJPO-BOHVBHFʣΛ࢖༻͠ ·͢ɻ ୈ**෦ɹσʔλϕʔε෺ཧઃܭͷΞϯνύλʔϯ ষɹϥ΢ϯσΟϯάΤϥʔʢؙΊޡࠩʣ ষɹαʔςΟϫϯϑϨʔόʔʢͷϑϨʔόʔʣ
ষɹϑΝϯτϜϑΝΠϧʢݬͷϑΝΠϧʣ ষɹΠϯσοΫεγϣοτΨϯʢҋӢΠϯσοΫεʣ

σʔλϕʔεͰ͸ɺσʔλͷ௥Ճ΍ݕࡧͳͲΛߦ͍·͢ɻ42-ͷΫΤϦ͸ɺ 4&-&$5ɺ61%"5&ɺ%&-&5&εςʔτϝϯτͳͲͷσʔλૢ࡞ݴޠʢ%.-ɿ %BUB.BOJQVMBUJPO-BOHVBHFʣͰߏ੒͞Ε·͢ɻ ୈ***෦ɹΫΤϦͷΞϯνύλʔϯ ষɹϑΟΞɾΦϒɾδɾΞϯϊ΢ϯʢڪාͷVOLOPXOʣ ষɹΞϯϏΪϡΞεάϧʔϓʢᐆດͳάϧʔϓʣ ষɹϥϯμϜηϨΫγϣϯ ষɹϓΞϚϯζɾαʔνΤϯδϯʢශऀͷαʔνΤϯδϯʣ ষɹεύήοςΟΫΤϦ ষɹΠϯϓϦγοτΧϥϜʢ҉໧ͷྻʣ

42-͸͍͍ͨͯɺଞͷݴޠʢ1ZUIPOɺ+BWBɺ$ɺ$ ɺ$ɺ+BWB4DSJQUɺ &MJYJSͳͲʣͰ։ൃ͞ΕͨΞϓϦέʔγϣϯͱڞʹ࢖༻͞Ε·͢ɻΞϓϦέʔγ ϣϯͰ42-Λ༻͍Δํ๏ʹ͸ɺద੾ͳ΋ͷͱͦ͏Ͱͳ͍΋ͷ͕͋Γ·͢ɻ͜ͷ ύʔτͰ͸ɺΑ͋͘ΔࣦഊྫΛ঺հ͠·͢ɻ ୈ*7෦ɹΞϓϦέʔγϣϯ։ൃͷΞϯνύλʔϯ ষɹϦʔμϒϧύεϫʔυʢಡΈऔΓՄೳύεϫʔυʣ ষɹ42-ΠϯδΣΫγϣϯ ষɹγϡʔυΩʔɾχʔτϑϦʔΫʢٙࣅΩʔܿบ঱ʣ ষɹγʔɾϊʔɾΤϏϧʢष͍΋ͷʹ֖ʣ
ষɹσΟϓϩϚςΟοΫɾΠϛϡχςΟʢ֎ަಛݖʣ ষɹελϯμʔυɾΦϖϨʔςΟϯάɾϓϩγʔδϟʢ͞ͼ͍ͭͨ։ൃඪ४ʣ 新規書き下ろしの章

ୈ*෦ ୈ**෦ ୈ***෦ ୈ*7෦ ୈ7෦ ෇࿥" σʔλϕʔε࿦ཧઃܭͷΞϯνύλʔϯ σʔλϕʔε෺ཧઃܭͷΞϯνύλʔϯ ΫΤϦͷΞϯνύλʔϯ ΞϓϦέʔγϣϯ։ൃͷΞϯνύλʔϯ
Ϙʔφεɿ֎෦ΩʔͷϛχɾΞϯνύλʔϯ ਖ਼نԽͷϧʔϧ ෦ ෦ߏ੒

ࢲ͸͜Ε·Ͱɺ֎෦Ωʔʹ͍ͭͯͷ࣭໰ʹେྔʹ౴͖͑ͯ·ͨ͠ɻͦͷ݁Ռɺ௥ ՃͷϛχɾΞϯνύλʔϯΛ͍ͭ͘΋ݟ͚ͭͨͷͰɺϘʔφεͱͯ͜͜͠Ͱఏڙ ͢Δ͜ͱʹ͠·ͨ͠ɻ֎෦ΩʔͷҰൠతͳ࢖༻ʹؔ͢ΔϛχɾΞϯνύλʔϯ ͱɺ.Z42-Ͱͷ֎෦Ωʔͷ࢖༻ʹؔ͢ΔϛχɾΞϯνύλʔϯʹ෼͚ɺͭͷ ষͰઆ໌͍͖ͯ͠·͢ɻ ষɹඪ४42-ʹ͓͚Δ֎෦Ωʔͷޡͬͨ࢖͍ํ ষɹ.Z42-ʹ͓͚Δ֎෦Ωʔͷޡͬͨ࢖͍ํ ෇࿥"ɹਖ਼نԽͷϧʔϧ ୈ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 WHERE account_id REGEXP '\\b12\\b'; 正規表現を使った単語境界へのマッチ
インデックスが使えない # " %

ಛఆͷ੡඼ʹؔ࿈͢ΔΞΧ΢ϯτͷݕࡧ 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の長さ次第で関連付けられる数が左右される # " %

ϓϩδΣΫτͰΞϯνύλʔϯ͕࢖ΘΕ͍ͯΔͱ͖ɺͦΕΛ࡯஌͢ΔͨΊͷख͕ ͔Γ͕͋Γ·͢ɻ௚໘͍ͯ͠Δ໰୊ͷछྨ΍ɺϝϯόʔؒͷձ࿩ͰͷԿؾͳ͍ݴ ༿͕ɺΞϯνύλʔϯͷଘࡏʹؾͮ͘ώϯτʹͳΓ·͢ɻ Ξϯνύλʔϯͷݟ͚ͭํ

ʮ͜ͷϦετͰαϙʔτ͠ͳ͚Ε͹ͳΒͳ͍࠷େͷΤϯτϦ਺͸ʁʯ ʮ42-Ͱ୯ޠڥքΛҰகͤ͞Δํ๏Λ஌ͬͯΔʁʯ ʮϦετͷཁૉʹઈର࢖ΘΕͳ͍จࣈͬͯԿ͚ͩͬʁʯ Ξϯνύλʔϯͷݟ͚ͭํ

ϧʔϧʹ͸ྫ֎͕͖ͭ΋ͷͰ͢ɻ௨ৗ͸ΞϯνύλʔϯͰ͋ΔͱࢥΘΕΔΞϓϩ ʔνͰ΋ɺঢ়گʹΑͬͯ͸ͦΕ͕ద੾Ͱ͋Δ৔߹΍ɺଧͯΔखͷͳ͔Ͱ͸Ұ൪· ͠ͳ΋ͷɺͱ͍͏৔߹͕͋Γ·͢ɻ ΞϯνύλʔϯΛ༻͍ͯ΋Α͍৔߹

͋ΔछͷΫΤϦͷύϑΥʔϚϯε޲্ͷͨΊʹɺςʔϒϧߏ଄ʹඇਖ਼نԽ ʢEFOPSNBMJ[BUJPOʣΛద༻͢Δ৔߹͕͋Γ·͢ɻϦετΛΧϯϚ۠੾Γͷจࣈ ྻͱͯ֨͠ೲ͢Δ͜ͱ΋ɺඇਖ਼نԽͷҰྫͰ͢ɻ ͨͩ͠ɺඇਖ਼نԽ͸ɺे෼ʹݕ౼͔ͯ͠Β࠾༻͠ͳ͚Ε͹ͳΓ·ͤΜɻ·ͣ͸ ਖ਼نԽ͞Εͨςʔϒϧߏ଄ͷ࠾༻Λݕ౼͠·͠ΐ͏ɻਖ਼نԽ͞Εͨߏ଄ͷ΄͏ ͕ɺΞϓϦέʔγϣϯίʔυͷॊೈੑΛߴΊɺσʔλͷ੔߹ੑΛอͭॿ͚ʹ΋ ͳΔ͔ΒͰ͢ɻ ΞϯνύλʔϯΛ༻͍ͯ΋Α͍৔߹

୯ͳΔʮ΂͔ΒͣूʯͰ͸ͳ͘ʮύλʔϯຊʯ ຊॻʹ͸ΞϯνύλʔϯΛద༻ͯ͠΋ྑ͍ঢ়گͷઆ໌΋͋ͬ ͯ޷ײ͕࣋ͯ·͢ɻ ུ ͜ͷຊ͸୯ͳΔʮ΂͔ΒͣूʯͰ ͸ͳ͘ʮύλʔϯຊʯ͔ͩΒͰ͢ɻίϯςΩετ΍੍໿͕ҟ ͳΕ͹ಋ͔ΕΔղ๏΋ҟͳΔͱ͍͏Θ͚Ͱ͢ɻ IUUQTZPKJLIBUFOBCMPHKQFOUSZ

ΞϯνύλʔϯʹؕΒͳ͍Α͏ʹͯ͠ɺ๯಄ͷλεΫΛղܾ͢ΔͨΊͷ๬·͍͠ ํ๏Λ঺հ͠·͢ɻ ղܾࡦ

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 するだけ

֎෦ΩʔΛ༻͍ͯผςʔϒϧͷ஋Λࢀর͢Δ͜ͱͰɺೖྗͷଥ౰ੑݕূΛߦ͑· ͢ɻ$POUBDUTςʔϒϧͷBDDPVOU@JEྻ͔Β"DDPVOUTςʔϒϧͷBDDPVOU@JEྻ ΁ͷࢀরΛએݴ͠ɺσʔλϕʔεʹࢀর੔߹ੑΛอূͤ͞·͢ɻ֎෦Ωʔ੍໿ʹ Αͬͯɺަࠩςʔϒϧʹ͸"DDPVOUTςʔϒϧʹଘࡏ͢ΔΞΧ΢ϯτ*%ͷΈ͕֨ ೲ͞ΕΔΑ͏ʹͳΓ·͢ɻ ·ͨɺ42-ͷσʔλܕʹΑͬͯೖྗ಺༰Λ੍ݶͰ͖·͢ɻྫ͑͹ɺϦετͷ֤Τ ϯτϦͷ༗ޮ஋Λ੔਺஋΍೔෇ʹ͍ͨ͠৔߹ɺྻʹରͯ͠*/5&(&3΍%"5&ͱ ͍ͬͨσʔλܕΛએݴ͢Δ͜ͱͰɺ͢΂ͯͷΤϯτϦ͕͜ΕΒσʔλܕͷ஋Ͱ͋ Δ͜ͱΛอূͰ͖·͢ɻ͜ΕͰɺʮCBOBOBʯͷΑ͏ͳφϯηϯεͳೖྗΛड͚ ෇͚ͳ͍Α͏ʹͰ͖·͢ɻ
ΞΧ΢ϯτ*%ͷଥ౰ੑݕূ

֤ΤϯτϦΛݸผͷߦʹ֨ೲ͢ΔͷͰɺ۠੾Γจࣈ͸ෆཁͰ͢ɻ΋͏ೖྗ಺༰ʹ ۠੾Γจࣈͱಉ͡จࣈؚ͕·Ε͍ͯΔ͔Ͳ͏͔Λ৺഑͢Δඞཁ͸͋Γ·ͤΜɻ ֤ΤϯτϦ͸ަࠩςʔϒϧͷݸผͷߦʹ֨ೲ͞Ε͍ͯ·͢ɻ੍ݶ͕͋Δͱ͢Ε ͹ɺͭͷςʔϒϧʹ෺ཧతʹ֨ೲͰ͖Δߦ਺ͷΈͰ͢ɻ ۠੾Γจࣈͷબ୒Ϧετͷ௕͞ͷ੍ݶ

͍͔ͭ͘ͷষͷ຤ඌʹ͸ʮϛχɾΞϯνύλʔϯʯͱ͍͏અΛ௥Ճ͠·ͨ͠ɻϛ χɾΞϯνύλʔϯͰ͸ɺ৽ͨͳʮΑ͋͘ΔࣦഊʯΛ؆୯ʹऔΓ্͛ɺͦΕΛආ ͚ΔͨΊʹ࠾༻Ͱ͖ΔʮखͬऔΓૣ͍ରॲࡦʯ΋঺հ͠·͢ɻ ষ຤ͷϛχɾΞϯνύλʔϯ

ϛχɾΞϯνύλʔϯɿ$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で複数の行に見せかけるトリック # " %

ୈষδΣΠ΢ΥʔΫʢ৴߸ແࢹʣͷ݁࿦ ͻͱͭͻͱͭͷ஋͸ݸผͷߦͱྻʹ֨ೲ͠·͠ΐ͏ɻ

۪ऀ͸ܦݧʹֶͼɺݡऀ͸ྺ࢙ʹֶͿ ᴷΦοτʔɾϑΥϯɾϏεϚϧΫ

ॾ܅͸ࣗΒͷܦݧ͔Β͍͘Βֶ͔Ϳ͜ͱ͕Ͱ͖Δͱ ͍͏ɺશ۪͔͘ͳߟ͑Ͱ͋Ζ͏͕ɺ༨͸Ή͠Ζଞਓ ͷࣦഊΛֶͿ͜ͱͰɺࣗ෼ͷࣦഊΛճආ͢Δ͜ͱΛ ޷Ήɻ ᴷΦοτʔɾϑΥϯɾϏεϚϧΫ IUUQTQPT fi FDPN!NUDFEBSQHPR/

ʮѱ͍͜ͱʯΛ·ͱΊͨҙٛ ͜ͷຊͷૉ੖Β͍͠ͱ͜Ζ͸ɺΑ͘ݟΔʮѱ͍ʯํ๏Λ ʮѱ͍͜ͱʯͱͯ͠·ͱΊͯ͘Εͨ͜ͱͰ͢ɻ IUUQTCMFJTUJGUIBUFOBCMPHDPNFOUSZ42-&"&#&&&#'&#$&#

ࣾ಺ಡॻձʹ޲͍͍ͯΔຊͰ͢ ֤ষ͕ಠཱ͍ͯ͠ΔʢͲ͔͜ΒͰ΋ࢀՃͰ͖Δʣ ࣾ಺ͳΒͰ͸ͷࣦഊͷڞ༗͕Ͱ͖Δ

͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠

SQLアンチパターン第2版 データベースプログラミングで陥りがちな失敗とその対策 / Intr...

SQLアンチパターン第2版 データベースプログラミングで陥りがちな失敗とその対策 / Intro to SQL Antipatterns 2nd

More Decks by Takuto Wada

Other Decks in Programming

Featured

Transcript

SQLアンチパターン第2版データベースプログラミングで陥りがちな失敗とその対策 / Intr...

SQLアンチパターン第2版データベースプログラミングで陥りがちな失敗とその対策 / Intro to SQL Antipatterns 2nd