MySQLがゆるふわなのはどう考えてもSQLモードが悪い！

スライド内URL
- http://dev.mysql.com/doc/refman/5.6/ja/sql-mode.html
- http://dev.mysql.com/doc/refman/5.6/ja/faqs-sql-modes.html
- https://www.softel.co.jp/blogs/tech/archives/4628
- https://www.seeds-std.co.jp/seedsblog/1035.html
- http://dev.mysql.com/doc/refman/5.6/ja/sql-mode.html
- http://www.songmu.jp/riji/entry/2015-07-08-kamipo-traditional.html
- http://yoku0825.blogspot.jp/2015/07/kamipo-traditional-strictalltables.html
- http://www.songmu.jp/riji/entry/2015-07-20-insert-ignore.html

Transcript

1. View Slide

2. ࡶίϥ͢Έ·ͤΜɻಋೖͰ͢
   • DATEʹෆਖ਼ͳܗࣜΛೖΕΔͱ0000-00-00
   • VARCHARͷ௕੍͞ݶΛ௒͑Δͱ੾ΓऔΒΕΔ
   • Φʔόʔϑϩʔ͢Δͱ஋ؙ͕ΊΒΕΔ
   • AIͳΧϥϜʹ0Λૠೖ͢Δͱࣗಈ࠾൪͞ΕΔ

   etc, etc…
   

   View Slide

3. ࠓ೥౓ͷࢲͷҧ࿨ײ
   • ৽ଔݚम
   • VARCHARͰ௕੍͞ݶΛ௒͑ͨΒΤϥʔग़ͨʂʁ
   • ๭ΞϓϦʹͯ൚༻όϦσʔγϣϯͷ࣮૷
   • Φʔόʔϑϩʔͨ͠Βͦ΋ͦ΋ΤϥʔͰྑ͘ͳ͍ʁ
   • ෆਖ਼ͳ೔෇ͬͯͦ΋ͦ΋DBଆͰ஄͚ͳ͍ͷ…ʁ
   • αʔόҠઃ
   • SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

   ͬͯͳΜ͚ͩͬɾɾɾʁ( ˘ω˘)
   

   View Slide

4. αʔόʔSQLϞʔυ
   ެࣜϚχϡΞϧ / en
   

   View Slide

5. αʔόʔSQLϞʔυ #ͱ͸
   • αʔόʔSQLϞʔυ͸ɺMySQLͰαϙʔτ͞
   ΕΔSQLߏจɺ͓Αͼ࣮ߦ͞ΕΔσʔλଥ౰ੑ
   νΣοΫͷछྨΛఆٛ͠·͢ɻ͜ΕʹΑΓɺ
   MySQLΛ͞·͟·ͳ؀ڥͰ࢖༻ͨ͠Γɺ
   MySQLΛ΄͔ͷσʔλϕʔεαʔόʔͱҰॹ
   ʹ࢖༻ͨ͠Γ͢Δ͜ͱ͕ɺ͞Βʹ༰қʹͳΓ
   ·͢ɻ
   A.3. MySQL 5.6 FAQ: αʔόʔ SQL Ϟʔυ
   

   View Slide

6. DATEʹෆਖ਼஋ΛೖΕΔͱ0000-00-00
   • ALLOW_INVALID_DATES
   • αʔόʔ͸ɺ݄ͱ೔ͷ஋͕ͦΕͧΕ1͔Β12·ͨ͸1
   ͔Β31ͷൣғʹ͋Δ͜ͱ͚ͩͰͳ͘ɺਖ਼͍͠஋Ͱ͋
   Δ͜ͱΛཁٻ͠·͢ɻݫີϞʔυ͕ແޮʹͳ͍ͬͯΔ
   ͱɺ'2004-04-31'ͷΑ͏ͳແޮͳ೔෇͸'0000-00-00'
   ʹม׵͞Εɺܯࠂϝοηʔδ͕දࣔ͞Ε·͢ɻݫີ
   Ϟʔυ͕༗ޮͳͱ͖͸ɺແޮͳ೔෇ʹΑͬͯΤϥʔ͕
   ൃੜ͠·͢ɻ
   

   View Slide

7. VARCHARͷ௕੍͞ݶΛ௒͑Δͱ etc
   • τϥϯβΫγϣϯςʔϒϧ(ex. InnoDB)ͷ৔
   ߹ɺ STRICT_ALL_TABLES ·ͨ͸
   STRICT_TRANS_TABLES ͷ͍ͣΕ͔͕༗ޮͳ
   ͱ͖ɺσʔλมߋεςʔτϝϯτ಺ͷແޮͳ
   ஋·ͨ͸ܽམͨ͠஋ʹରͯ͠Τϥʔ͕ൃੜ͠
   ·͢ɻεςʔτϝϯτ͸தࢭ͞Εͯϩʔϧόο
   Ϋ͞Ε·͢ɻ
   

   View Slide

8. ~MySQL5.5, MySQL5.6~
   • STRICT_TRANS_TABLES(5.6~)
   • ஋Λࢦఆͨ͠ͱ͓ΓʹτϥϯβΫγϣϯςʔϒϧʹૠೖͰ͖ͳ͍৔߹ɺ
   εςʔτϝϯτΛதࢭ͠·͢ɻ
   • NO_ENGINE_SUBSTITUTION(5.6.6~)
   • CREATE TABLE ·ͨ͸ ALTER TABLE ͳͲͷεςʔτϝϯτ͕ແޮ·ͨ
   ͸ίϯύΠϧ͞Ε͍ͯͳ͍ετϨʔδΤϯδϯΛࢦఆͨ͠ͱ͖ɺ໨తͷ
   Τϯδϯ͕ར༻Ͱ͖ͳ͍৔߹ʹΤϥʔ͕ൃੜ
   MySQL5.6͸ઃఆͷσϑΥϧτ஋͕5.5ͱҧ͏ at softelϝϞ
   MySQL5.6ʹ͍͔ͯͭ͘͠ͷSQLͰΤϥʔ͕ग़ΔΑ͏ʹͳͬͨ
   

   View Slide

9. ֬ೝํ๏ʢͲͪΒ΋MySQL5.6.20~ʣ
   !
   • ී௨͜͏ˣͳΔɹɹɹɹɹ ๭ Ξ ϓ Ϧ
   

   View Slide

10. ૊Έ߹ΘͤͰҰׅࢦఆ
    • ૊Έ߹Θͤ SQL Ϟʔυ
    • ଞDBαʔόͷΤϛϡϨʔτ
    • DB2, MSSQL, ORACLE, POSTGRESQL, etc…
    • TRADITIONAL(ݫ֨Ϟʔυ+α)
    • STRICT_TRANS_TABLES, STRICT_ALL_TABLES,
    NO_ZERO_IN_DATE, NO_ZERO_DATE,
    ERROR_FOR_DIVISION_BY_ZERO,
    NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION
    

    View Slide

11. ݁ہԿ͕ྑ͍ͷ͞ʁ
    

    View Slide

12. kamipo TRADITIONALͰ݁࿦...ʁ
    • ONLY_FULL_GROUP_BY
    • ඪ४ SQL ͷಈ࡞͕༗ޮʹͳΓ·͢ɻGROUP BY ۟Ͱ໊લ͕
    ࢦఆ͞Ε͍ͯͳ͍ΧϥϜ͸ɺू໿ؔ਺Ͱғ·ͳ͚Ε͹ɺબ୒
    Ϧετ·ͨ͸ HAVING ۟Ͱ࢖༻Ͱ͖·ͤΜ
    • ݸਓతݟղ
    • ͵Δ·౬(MySQL)ͰҭͬͨͷͰͿͬͪΌ͚ෆศ
    • σʔλͷ੔߹ੑʹؔΘΒͳ͍ͷͰϝϦοτΛײ͡ͳ͍...
    

    View Slide

13. inoue TRADITIONAL
    !
    ↓
    !
    TRADITIONAL
    +
    NO_AUTO_VALUE_ON_ZERO
    

    View Slide

14. Q. طʹෆਖ਼ͳ஋ೖͬͯΔ৔߹͸ʁ
    • ͋͘·Ͱσʔλૠೖɺߋ৽࣌ͷݕূ
    • 0000-00-00 00:00:00ͷϨίʔυΛೖΕͯ࠶ىಈ

    ˠ ੒ޭɻෆਖ਼ͳϨίʔυ΋࢒ͬͯΔ
    • ޙ෇ͰSQLϞʔυΛม͑Δ͜ͱ͸ՄೳͬͪΌՄೳ
    • ݱ࣮
    • Ͱ΋ෆਖ਼ͳ஋ΛೖΕΑ͏ͱ͢ΔSQL͢΂ͯ௚͢ඞཁ͕͋Δ
    

    View Slide

15. ·ͱΊ
    • ઃఆ࣍ୈͰMySQL΋͍ͩͿݻ͘Ͱ͖Δ
    • ৽ن͸νϟϯεɺͳΔ΂͘ݻΊઃఆͰ
    • DBଆͰෆਖ਼஋͸஄͘ɻຊདྷͷϩδοΫʹूதͰ͖Δ
    • INSERT/UPDATE IGNORE͸ͭΒ͍
    • MyISAMʢඇτϥϯβΫγϣϯςʔϒϧʣ͸ͭΒ͍
    

    View Slide

16. ࢀߟهࣄ͞·
    • ϧʔΫʂMySQLͰ͸kamipo TRADITIONALΛ࢖͑ʂ
    • kamipo traditional (ͱ͍͏͔STRICT_ALL_TABLES) Ͱ͸๷͛ͳ͍
    MyISAMͱ͍͏໊ͷԽ͚෺
    • kamipo TRADITIONALͰ͸๷͛ͳ͍INSERT IGNOREͱ͍͏໊ͷԽ͚෺
    

    View Slide