Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

Leko
July 31, 2015

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

Leko

July 31, 2015
Tweet

More Decks by Leko

Other Decks in Technology

Transcript

  1. ࠓ೥౓ͷࢲͷҧ࿨ײ • ৽ଔݚम • VARCHARͰ௕੍͞ݶΛ௒͑ͨΒΤϥʔग़ͨʂʁ • ๭ΞϓϦʹͯ൚༻όϦσʔγϣϯͷ࣮૷ • Φʔόʔϑϩʔͨ͠Βͦ΋ͦ΋ΤϥʔͰྑ͘ͳ͍ʁ •

    ෆਖ਼ͳ೔෇ͬͯͦ΋ͦ΋DBଆͰ஄͚ͳ͍ͷ…ʁ • αʔόҠઃ • SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
 ͬͯͳΜ͚ͩͬɾɾɾʁ( ˘ω˘)
  2. VARCHARͷ௕੍͞ݶΛ௒͑Δͱ etc • τϥϯβΫγϣϯςʔϒϧ(ex. InnoDB)ͷ৔ ߹ɺ STRICT_ALL_TABLES ·ͨ͸ STRICT_TRANS_TABLES ͷ͍ͣΕ͔͕༗ޮͳ

    ͱ͖ɺσʔλมߋεςʔτϝϯτ಺ͷແޮͳ ஋·ͨ͸ܽམͨ͠஋ʹରͯ͠Τϥʔ͕ൃੜ͠ ·͢ɻεςʔτϝϯτ͸தࢭ͞Εͯϩʔϧόο Ϋ͞Ε·͢ɻ
  3. ~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ͰΤϥʔ͕ग़ΔΑ͏ʹͳͬͨ
  4. ૊Έ߹ΘͤͰҰׅࢦఆ • ૊Έ߹Θͤ 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
  5. kamipo TRADITIONALͰ݁࿦...ʁ • ONLY_FULL_GROUP_BY • ඪ४ SQL ͷಈ࡞͕༗ޮʹͳΓ·͢ɻGROUP BY ۟Ͱ໊લ͕

    ࢦఆ͞Ε͍ͯͳ͍ΧϥϜ͸ɺू໿ؔ਺Ͱғ·ͳ͚Ε͹ɺબ୒ Ϧετ·ͨ͸ HAVING ۟Ͱ࢖༻Ͱ͖·ͤΜ • ݸਓతݟղ • ͵Δ·౬(MySQL)ͰҭͬͨͷͰͿͬͪΌ͚ෆศ • σʔλͷ੔߹ੑʹؔΘΒͳ͍ͷͰϝϦοτΛײ͡ͳ͍...
  6. Q. طʹෆਖ਼ͳ஋ೖͬͯΔ৔߹͸ʁ • ͋͘·Ͱσʔλૠೖɺߋ৽࣌ͷݕূ • 0000-00-00 00:00:00ͷϨίʔυΛೖΕͯ࠶ىಈ
 ˠ ੒ޭɻෆਖ਼ͳϨίʔυ΋࢒ͬͯΔ •

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