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

by Takuto Wada

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

ࢲ͸͜Ε·Ͱɺ֎෦Ωʔʹ͍ͭͯͷ࣭໰ʹେྔʹ౴͖͑ͯ·ͨ͠ɻͦͷ݁Ռɺ௥ ՃͷϛχɾΞϯνύλʔϯΛ͍ͭ͘΋ݟ͚ͭͨͷͰɺϘʔφεͱͯ͜͜͠Ͱఏڙ ͢Δ͜ͱʹ͠·ͨ͠ɻ֎෦ΩʔͷҰൠతͳ࢖༻ʹؔ͢ΔϛχɾΞϯνύλʔϯ ͱɺ.Z42-Ͱͷ֎෦Ωʔͷ࢖༻ʹؔ͢ΔϛχɾΞϯνύλʔϯʹ෼͚ɺͭͷ ষͰઆ໌͍͖ͯ͠·͢ɻ ষɹඪ४42-ʹ͓͚Δ֎෦Ωʔͷޡͬͨ࢖͍ํ ষɹ.Z42-ʹ͓͚Δ֎෦Ωʔͷޡͬͨ࢖͍ํ ෇࿥"ɹਖ਼نԽͷϧʔϧ ୈ7෦ɹϘʔφεɿ֎෦ΩʔͷϛχɾΞϯνύλʔϯɺ෇࿥

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

ɹ$47ྻΛෳ਺ͷߦʹ෼ׂ͢Δ ɹʮࢲͷίϯϐϡʔλʔͰ͸ಈ࡞͍ͯ͠Δͷʹʯ ɹ#*(*/5͸े෼ʹେ͖͍ʁ ɹՁ֨ͷอଘ ɹ༧໿ޠ ɹ͢΂ͯͷྻʹΠϯσοΫεΛ࡞੒͢Δ ɹ/05*/ /6-- ɹϙʔλϒϧ42- ɹΫΤϦͰϥϯμϜʹෳ਺ߦΛऔಘ͢Δ ɹϋογϡจࣈྻΛ7"3$)"3ܕͰ֨ೲ͢Δ ɹҾ༻ූ಺ͷΫΤϦύϥϝʔλ ɹάϧʔϓ͝ͱͷࣗಈΠϯΫϦϝϯτ ɹߏจΤϥʔϝοηʔδղಡͷ͢͢Ί ɹ໊લͷมߋ ɹ.Z42-ͷετΞυϓϩγʔδϟ ֤ষ຤ͷϛχɾΞϯνύλʔϯʢશͯ৽نॻ͖ԼΖ͠ʣ

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

໊લ ໨త Ξϯνύλʔϯ Ξϯνύλʔϯͷݟ͚ͭํ ΞϯνύλʔϯΛ༻͍ͯ΋Α͍৔߹ ղܾࡦ ϛχɾΞϯνύλʔϯ ຊॻͷΞϯνύλʔϯͷߏ੒

Slide 22

Slide 22 text

໊લ ໨త Ξϯνύλʔϯ Ξϯνύλʔϯͷݟ͚ͭํ ΞϯνύλʔϯΛ༻͍ͯ΋Α͍৔߹ ղܾࡦ ϛχɾΞϯνύλʔϯ ຊॻͷΞϯνύλʔϯͷߏ੒ 👉

Slide 23

Slide 23 text

δΣΠ΢ΥʔΫʢ৴߸ແࢹʣ ύλʔϯ໊ͷྫষδΣΠ΢ΥʔΫʢ৴߸ແࢹʣ

Slide 24

Slide 24 text

ύλʔϯ໊ͷ໾ׂਓʹɺͦͯ͠--.ʹ୹͍ݴ༿Ͱ఻ΘΔ

Slide 25

Slide 25 text

໊લ ໨త Ξϯνύλʔϯ Ξϯνύλʔϯͷݟ͚ͭํ ΞϯνύλʔϯΛ༻͍ͯ΋Α͍৔߹ ղܾࡦ ϛχɾΞϯνύλʔϯ ຊॻͷΞϯνύλʔϯͷߏ੒ 👉

Slide 26

Slide 26 text

ղܾ͢΂͖λεΫͰ͢ɻΞϯνύλʔϯͱ͸ɺλεΫͷղܾͷͨΊʹ༻͍ΒΕͨ ΋ͷͷɺٯʹ΋ͬͱଟ͘ͷ໰୊Λੜͤͯ͡͞͠·͏΋ͷͳͷͰ͢ɻ ໨త Α͔Εͱࢥͬͯཪ໨ʹग़ͯ͠·͏΋ͷ

Slide 27

Slide 27 text

͋ͳͨ͸ɺόά؅ཧΞϓϦέʔγϣϯͷ৽ػೳΛ։ൃ͍ͯ͠·͢ɻ੡඼ͷ࿈བྷ૭ ޱͱͯ͠ɺϢʔβʔΛਓࢦఆ͢ΔػೳͰ͢ɻ࠷ॳͷ࢓༷Ͱ͸ɺ֤੡඼ͷ࿈བྷઌ ͱͳΔϢʔβʔ͸ਓͷΈͰͨ͠ɻ ͱ͜Ζ͕Ҋͷఆɺޙʹͳͬͯͭͷ੡඼ʹෳ਺ϢʔβʔΛ࿈བྷઌͱͯ͠ొ࿥Ͱ͖ ΔΑ͏ʹͯ͠ཉ͍͠ͱ͍͏ཁ๬͕ग़͖ͯ·ͨ͠ɻ ॳΊ͸ɺͦͷมߋ͸؆୯ͩͱࢥ͍·ͨ͠ɻྻʹΞΧ΢ϯτ*%Λ݅ͷΈ֨ೲ͢Δ ͷͰ͸ͳ͘ɺΧϯϚ۠੾ΓͰෳ਺ͷΞΧ΢ϯτ*%ͷϦετΛ֨ೲͰ͖ΔΑ͏ʹ ͢Ε͹Α͍ͱߟ͑ɺͦͷ௨ΓʹมߋΛߦ͍·ͨ͠ɻ ໨తɿෳ਺ͷ஋Λ࣋ͭଐੑΛ֨ೲ͢Δ

Slide 28

Slide 28 text

໊લ ໨త Ξϯνύλʔϯ Ξϯνύλʔϯͷݟ͚ͭํ ΞϯνύλʔϯΛ༻͍ͯ΋Α͍৔߹ ղܾࡦ ϛχɾΞϯνύλʔϯ ຊॻͷΞϯνύλʔϯͷߏ੒ 👉

Slide 29

Slide 29 text

Ξϯνύλʔϯͦͷ΋ͷΛղઆ͠·͢ɻλεΫͷղܾࡦͱͯ͠Α͘༻͍ΒΕΔํ ๏͕ɺͦͷੑ࣭ʹΑͬͯ༧ظͤ͵݁ՌΛট͖ɺΞϯνύλʔϯʹͳͬͯ͠·͏ܦ ҢΛઆ໌͠·͢ɻ Ξϯνύλʔϯ Α͔Εͱࢥͬͯཪ໨ʹग़ͯ͠·͏΋ͷ

Slide 30

Slide 30 text

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); มߋલͷςʔ ϒϧఆٛ

Slide 31

Slide 31 text

ΞϯνύλʔϯɿΧϯϚ۠੾ΓϑΥʔϚοτͷϦετΛ֨ೲ͢Δ 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'); カンマ区切りで多対多関連を格納してしまう # " %

Slide 32

Slide 32 text

ಛఆͷΞΧ΢ϯτʹؔ࿈͢Δ੡඼ͷݕࡧ SELECT * FROM Products WHERE account_id REGEXP '\\b12\\b'; 正規表現を使った単語境界へのマッチインデックスが使えない # " %

Slide 33

Slide 33 text

ಛఆͷ੡඼ʹؔ࿈͢ΔΞΧ΢ϯτͷݕࡧ 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に正規表現を使わなければならない…… # " %

Slide 34

Slide 34 text

ू໿ΫΤϦͷ࡞੒ SELECT product_id, LENGTH(account_id) - LENGTH(REPLACE(account_id, ',', '')) + 1 AS contacts_per_product FROM Products; カンマ区切りの文字列の長さからカンマ以外の文字列の長さを引いたものを計算するような、本来は不要なトリックを使わなければならない # " %

Slide 35

Slide 35 text

ಛఆͷ੡඼ʹؔ࿈͢ΔΞΧ΢ϯτͷߋ৽ 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つ発行しなければならない。古いリストの取得と、更新したリストの保存 # " %

Slide 36

Slide 36 text

ΞΧ΢ϯτ*%ͷଥ౰ੑݕূ INSERT INTO Products (product_id, product_name, account_id) VALUES (DEFAULT, 'Visual TurboBuilder', '12,34,banana'); アカウントIDではない不正な文字列も入ってしまう # " %

Slide 37

Slide 37 text

۠੾Γจࣈͷબ୒ ੔਺஋ͷϦετͰ͸ͳ͘จࣈྻ஋ͷϦετΛ֨ೲ͢Δ৔߹ɺϦετͷݸʑͷೖ ྗ஋ʹ΋۠੾ΓจࣈʢΧϯϚͳͲʣؚ͕·ΕΔ͜ͱ͕͋Γ·͢ɻ ͜ͷͨΊɺΧϯϚΛ۠੾Γจࣈʹࢦఆ͍ͯ͠Δ৔߹ʹ͸ɺೖྗ͞Εͨจࣈྻͷ Ұ෦ͱͯ͠ΧϯϚ͕࢖ΘΕ͍ͯΔͷ͔ɺ֤ཁૉΛ۠ผ͢ΔͨΊͷ۠੾Γจࣈͱ͠ ͯ࢖ΘΕ͍ͯΔͷ͔͕ෆ໌֬ʹͳΔՄೳੑ͕͋Γ·͢ɻ ଞͷ۠੾ΓจࣈΛࢦఆ͢Δ͜ͱ΋Ͱ͖·͕͢ɺͦͷ۠੾Γจࣈ͕ೖྗจࣈྻͱ͠ ͯ࢖༻͞Εͳ͍ͱ͍͏อূ͸Ͱ͖·ͤΜɻ # " %

Slide 38

Slide 38 text

Ϧετͷ௕͞ͷ੍ݶ 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の長さ次第で関連付けられる数が左右される # " %

Slide 39

Slide 39 text

໊લ ໨త Ξϯνύλʔϯ Ξϯνύλʔϯͷݟ͚ͭํ ΞϯνύλʔϯΛ༻͍ͯ΋Α͍৔߹ ղܾࡦ ϛχɾΞϯνύλʔϯ ຊॻͷΞϯνύλʔϯͷߏ੒ 👉

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

໊લ ໨త Ξϯνύλʔϯ Ξϯνύλʔϯͷݟ͚ͭํ ΞϯνύλʔϯΛ༻͍ͯ΋Α͍৔߹ ղܾࡦ ϛχɾΞϯνύλʔϯ ຊॻͷΞϯνύλʔϯͷߏ੒ 👉

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

໊લ ໨త Ξϯνύλʔϯ Ξϯνύλʔϯͷݟ͚ͭํ ΞϯνύλʔϯΛ༻͍ͯ΋Α͍৔߹ ղܾࡦ ϛχɾΞϯνύλʔϯ ຊॻͷΞϯνύλʔϯͷߏ੒ 👉

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

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) ); ղܾࡦɿަࠩςʔ ϒϧΛ࡞੒͢Δ

Slide 49

Slide 49 text

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; 正規表現はもう不要で、インデックスも活用できる正規表現はもう不要で、インデックスも活用できる

Slide 50

Slide 50 text

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; シンプルに集約関数を使うだけでいい。カンマ区切りの文字列の長さからカンマ以外の文字列の長さを引いたものを計算するようなトリックは不要シンプルに集約関数を使うだけでいい。カンマ区切りの文字列の長さからカンマ以外の文字列の長さを引いたものを計算するようなトリックはもう不要

Slide 51

Slide 51 text

ಛఆͷ੡඼ʹؔ࿈͢ΔΞΧ΢ϯτͷߋ৽ 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 するだけ

Slide 52

Slide 52 text

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

Slide 53

Slide 53 text

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

Slide 54

Slide 54 text

໊લ ໨త Ξϯνύλʔϯ Ξϯνύλʔϯͷݟ͚ͭํ ΞϯνύλʔϯΛ༻͍ͯ΋Α͍৔߹ ղܾࡦ ϛχɾΞϯνύλʔϯ ຊॻͷΞϯνύλʔϯͷߏ੒ 👉

Slide 55

Slide 55 text

Slide 56

Slide 56 text

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

Slide 57

Slide 57 text

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

Slide 58

Slide 58 text

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

Slide 59

Slide 59 text

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

Slide 60

Slide 60 text

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

Slide 61

Slide 61 text

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

Slide 62

Slide 62 text

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

Slide 63

Slide 63 text

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