MySQLでbulk insert + on duplicate key updateしたい

MySQLでINSERT INTO hoge VALUES (...), (...), (...)のbulk insertでon duplicate key update(すでにレコードがあったらUPDATEで上書きする)って併用できるのかな？っていうのが気になったので調べてみたらできるみたい。VALUES関数というその目的のためだけに存在するような関数を使う。

こんな感じでテーブル作って

CREATE TABLE users (
    name VARCHAR(255) NOT NULL,
    age TINYINT UNSIGNED NOT NULL,
    PRIMARY KEY (name)
) ENGINE=InnoDB;

データをINSERTして

INSERT INTO users VALUES
  ('akuwano', 25), ('oinume', 24), ('oranie', 23);

SELECT * FROM users;
+---------+-----+
| name    | age |
+---------+-----+
| akuwano |  25 |
| oinume  |  24 |
| oranie  |  23 |
+---------+-----+

INSERT ... ON DUPLICATE KEY UPDATEで重複したレコードがあった場合に全てアップデートされるかな？

INSERT INTO users VALUES
  ('akuwano', 15), ('oinume', 14), ('oranie', 13)
  ON DUPLICATE KEY UPDATE age = VALUES(age);

mysql> SELECT * FROM users;
+---------+-----+
| name    | age |
+---------+-----+
| akuwano |  15 |
| oinume  |  14 |
| oranie  |  13 |
+---------+-----+

された！！

実践ハイパフォーマンスMySQL 第3版

ターミナルとキーボードだけでプルリクエストを送る

これはSending pull-request only with terminal and keyboardの日本語の記事です。

必要なもの

• Mac
• Terminal
• hub command (brew install hubでインストールしておく)

How to send pull-request

ブランチを作る

$ git checkout -b new-cool-feature

ソースを編集する

$ git commit -a

わざとgit pushしてエラーにして、正しいコマンドをpbcopyでクリップボードにコピー

$ git push 2>&1 | grep git | pbcopy

Cmd+Vでペーストしてpush

$ git push --set-upstream origin new-cool-feature

Counting objects: 3, done.
Delta compression using up to 8 threads.
Compressing objects: 100% (3/3), done.
Writing objects: 100% (3/3), 324 bytes | 0 bytes/s, done.
Total 3 (delta 2), reused 0 (delta 0)
To git@github.com:example/example-project.git
 * [new branch]      new-cool-feature -> new-cool-feature
Branch new-cool-feature set up to track remote branch new-cool-feature from origin.

hubコマンドでプルリクエストを作成

$ hub pull-request
https://github.com/example/example-project/pull/1

もしくは

$ hub browse

でブラウザでgithub.comを開いて画面からプルリクエストを作成

Sending pull-request only with terminal and keyboard

Requirements

• Mac
• Terminal
• hub command (brew install hub)

How to send pull-request

Make a new branch.

$ git checkout -b new-cool-feature

Editing source code and commit.

$ git commit -a

Push to remote and copy correct git push command into clipboard.

$ git push 2>&1 | grep git | pbcopy

Paste clipboard into terminal.

$ git push --set-upstream origin new-cool-feature

Counting objects: 3, done.
Delta compression using up to 8 threads.
Compressing objects: 100% (3/3), done.
Writing objects: 100% (3/3), 324 bytes | 0 bytes/s, done.
Total 3 (delta 2), reused 0 (delta 0)
To git@github.com:example/example-project.git
 * [new branch]      new-cool-feature -> new-cool-feature
Branch new-cool-feature set up to track remote branch new-cool-feature from origin.

Send pull-request by hub.

$ hub pull-request
https://github.com/example/example-project/pull/1

or open github.com with a browser and make a pull-request on page.

$ hub browse

Logging into dmm.com by Selenium

I use Selenium to log into dmm.com because dmm.com requires JavaScript enabled browser. Here is a sample code. You can see Firefox is launched and automatically logging into dmm.com if you use Firefox instead of PhantomJS.

login_dmm.py

#!/usr/bin/env python
# encoding: utf-8

import os
from selenium import webdriver

driver = webdriver.PhantomJS()
#driver = webdriver.Firefox()
driver.get("https://www.dmm.com/my/-/login/=/path=DRVESVwZTldRDlBRRFdIUwwIGFVfVEs_")
login_id = driver.find_element_by_id("login_id")
login_id.send_keys(os.environ["DMM_LOGIN_ID"])
password = driver.find_element_by_id("password")
password.send_keys(os.environ["DMM_LOGIN_PASSWORD"])
driver.find_element_by_tag_name("form").submit()
print(driver.page_source)

driver.quit()

Run

$ brew install phantomjs
$ pip install selenium
$ DMM_LOGIN_ID=your_id DMM_LOGIN_PASSWORD=your_password python ./login_dmm.py

Get auto increment values from MySQL information schema

Information schema is metadata about MySQL. We can obtain useful information from it. For example, I get current auto increment values by following SQL.

SELECT
  t.table_name, t.auto_increment
FROM
  information_schema.tables AS t
WHERE
  t.table_schema = 'your_db'
ORDER BY t.table_name;

+-------------------------------------+----------------+
| table_name                          | auto_increment |
+-------------------------------------+----------------+
| blog_categories                     |              4 |
| blog_posts                          |              4 |
| blog_post_categories                |              4 |
| blog_post_comments                  |              1 |
| images                              |              7 |
+-------------------------------------+----------------+

INFORMATION_SCHEMA database has much more useful tables like COLUMNS, GLOBAL_VARIABLES and so on. It's good to use it when you want to know much about MySQL.