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

知って欲しいSQLなこと

Sponsored · SiteGround - Reliable hosting with speed, security, and support you can count on.

 知って欲しいSQLなこと

中国地方DB勉強会 in 岡山のLT資料です

Avatar for soudai sone

soudai sone PRO

July 31, 2016
Tweet

More Decks by soudai sone

Other Decks in Technology

Transcript

  1. ϧʔϓͰINSERT $array = $this->get_hoge(); $this->db->begin(); for ($index = 0; $index

    < count($array); $index++) { $this->db->inset($array["{$index}"]) } $this->db->commit();
  2. ϧʔϓͰINSERT $array = $this->get_hoge(); $this->db->begin(); for ($index = 0; $index

    < count($array); $index++) { $this->db->inset($array["{$index}"]) } $this->db->commit(); ϧʔϓͷ਺͚ͩ*/4&35͕ݺ͹ΕΔ
  3. ϧʔϓͰINSERT $array = $this->get_hoge(); $this->db->begin(); for ($index = 0; $index

    < count($array); $index++) { $this->db->inset($array["{$index}"]) } $this->db->commit(); ϧʔϓͷ਺͚ͩ*/4&35͕ݺ͹ΕΔ ϧʔϓͷ਺͚ͩDPVOU ͕ݺ͹ΕΔ
  4. ϧʔϓͰINSERT INSERT INTO hoge (id,name) values (1,’sone1’), (2,’sone2’), (3,’sone3’), (4,’sone4’),

    (5,’sone5’), (6,’sone6’) … # ࠷ۙͷFWͳΒ഑ྻ౉͚ͩ͢Ͱ͍͍ this->db->inset($array);
  5. ϧʔϓͰINSERT $array = $this->get_hoge(); $this->db->begin(); $count = count($array); for ($index

    = 0; $index < $count; $index++) { if (empty($array[“{$index}”][“id”])) { $this->db->inset($array["{$index}"]) } else { $this->db->update($array["{$index}"]) } } $this->db->commit();
  6. ϧʔϓͰINSERT ——PostgreSQL INSERT INTO ςʔϒϧ໊ VALUES ('஋̍', '஋̎', ...ʣ ON

    CONFLICT ON CONSTRAINT ੍໿໊ DO UPDATE SET ΧϥϜ̍='஋', ΧϥϜ̎='஋', ...; ——MySQL INSERT INTO ςʔϒϧ໊ VALUES ('஋̍', '஋̎', …ʣ ON DUPLICATE KEY ※ࣗ෼Ͱࢦఆ͢Δ͜ͱ͕ग़དྷͳ͍ UPDATE ΧϥϜ̍='஋', ΧϥϜ̎='஋', ...;
  7. ϧʔϓͰINSERT $array = $this->get_hoge(); $this->db->begin(); for ($index = 0; $index

    < count($array); $index++) { $this->db->inset($array["{$index}"]) } $this->db->commit();
  8. N+1 $users = $this->db->users->get(); $this->db->begin(); foreach ($users as $user_id =>

    $user) { if ($user[“pref”] == ‘Ԭࢁ’) { $user[“pref”] = ‘େ౎ձ’ } elseif ($user[“pref”] == ‘޿ౡ’){ $user[“pref”] = ‘෱ࢁ’ } $this->db->update($user); } $this->db->commit();
  9. N+1 $users = $this->db->users->get(); $this->db->begin(); foreach ($users as $user_id =>

    $user) { if ($user[“pref”] == ‘Ԭࢁ’) { $user[“pref”] = ‘େ౎ձ’ } elseif ($user[“pref”] == ‘޿ౡ’){ $user[“pref”] = ‘෱ࢁ’ } $this->db->update($user); } $this->db->commit(); ͦ΋ͦ΋8)&3&۟ͰQSFGࢦఆͯ͠ 61%"5&ͳΒճ
  10. N+1 UPDATE users SET pref = CASE pref WHEN ‘Ԭࢁ’

    THEN ‘େ౎ձ’ WHEN ‘޿ౡ’ THEN ‘෱ࢁ’ ELSE pref END WHERE pref IN (‘Ԭࢁ’,’޿ౡ’)
  11. N+1 JE OBNF BHF  TPOF   UBLFUPNP 

     TPVEBJ  VTFS@JE CJSUIEBZ QSFG   ޿ౡ   ෱ࢁ   େ౎ձ VTFST VTFS@QSPQFSUJFT 
  12. N+1 UPDATE users AS u, (SELECT users.id AS id, CASE

    WHEN user_properties.birthday > ‘1996-07-30’ THEN ‘ϋλν’ ELSE age END AS age FROM users INNER JOIN user_properties ON user.id = user_properties.user_id AND user_properties.pref IN (‘෱ࢁ’,’ͳ͝΍’,’େ౎ձ’)) AS tmp SET u.age = tmp.age WHERE u.id = tmp.id
  13. N+1 JE OBNF BHF  TPOF   UBLFUPNP ϋλν

     TPVEBJ  VTFS@JE CJSUIEBZ QSFG   ޿ౡ   ෱ࢁ   େ౎ձ