{"id":8510,"date":"2020-10-16T12:09:29","date_gmt":"2020-10-16T03:09:29","guid":{"rendered":"https:\/\/www.gesource.jp\/weblog\/?p=8510"},"modified":"2020-10-14T22:11:45","modified_gmt":"2020-10-14T13:11:45","slug":"mysql%e3%81%a7%e6%9d%a1%e4%bb%b6%e3%81%ab%e4%b8%80%e8%87%b4%e3%81%99%e3%82%8b%e3%83%ac%e3%82%b3%e3%83%bc%e3%83%89%e3%81%8c%e3%81%aa%e3%81%91%e3%82%8c%e3%81%b0%e6%96%b0%e8%a6%8f%e7%99%bb%e9%8c%b2","status":"publish","type":"post","link":"https:\/\/www.gesource.jp\/weblog\/?p=8510","title":{"rendered":"MySQL\u3067\u6761\u4ef6\u306b\u4e00\u81f4\u3059\u308b\u30ec\u30b3\u30fc\u30c9\u304c\u306a\u3051\u308c\u3070\u65b0\u898f\u767b\u9332\u3001\u3042\u308c\u3070\u4f55\u3082\u3057\u306a\u3044"},"content":{"rendered":"

INSERT\u69cb\u6587\u306bIGNORE\u30ad\u30fc\u30ef\u30fc\u30c9\u3092\u4f7f\u7528\u3059\u308b<\/h2>\n

IGNORE\u30ad\u30fc\u30ef\u30fc\u30c9\u3092\u4f7f\u7528\u3059\u308b\u3068
\nUNIQUE\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u307e\u305f\u306fPRIMARY KEY\u5024\u3092\u8907\u88fd\u306b\u3059\u308b\u884c\u3088\u3063\u3066\u91cd\u8907\u30a8\u30e9\u30fc\u304c\u767a\u751f\u3057\u305f\u3068\u304d\u3001\u30b9\u30c6\u30fc\u30c8\u30e1\u30f3\u30c8\u306f\u4e2d\u6b62\u3055\u308c\u308b\u3002
\n\u91cd\u8907\u3059\u308b\u884c\u306f\u7834\u68c4\u3055\u308c\u3001\u30a8\u30e9\u30fc\u306f\u767a\u751f\u3057\u306a\u3044\u3002
\n(\u53c2\u8003)MySQL :: MySQL 5.6 \u30ea\u30d5\u30a1\u30ec\u30f3\u30b9\u30de\u30cb\u30e5\u30a2\u30eb :: 13.2.5 INSERT \u69cb\u6587<\/a><\/p>\n

\u91cd\u8907\u30c1\u30a7\u30c3\u30af\u306e\u5bfe\u8c61\u306fUNIQUE\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u307e\u305f\u306fPRIMARY KEY\u5024\u3067\u3042\u308a\u3001\u91cd\u8907\u30c1\u30a7\u30c3\u30af\u3059\u308b\u30d5\u30a3\u30fc\u30eb\u30c9\u3092\u6307\u5b9a\u3067\u304d\u306a\u3044\u3002<\/p>\n

\u4f8b\u3048\u3070\u3001\u6b21\u306e\u3088\u3046\u306a\u30c6\u30fc\u30d6\u30eb\u304c\u3042\u3063\u305f\u3068\u304d\u3001<\/p>\n

CREATE TABLE users (\n    id INT AUTO_INCREMENT NOT NULL,\n    name VARCHAR(255) UNIQUE,\n    email VARCHAR(255),\n    PRIMARY KEY(id)\n);\n<\/code><\/pre>\n
1\u56de\u76ee\u306f\u8ffd\u52a0\u3055\u308c\u308b\u3002<\/p>\n
INSERT IGNORE INTO users(name, email) VALUES('\u9234\u6728', 'suzuki1@example.com');\n<\/code><\/pre>\n
2\u56de\u76ee\u306f”\u9234\u6728”\u304c\u3059\u3067\u306b\u767b\u9332\u3055\u308c\u3066\u3044\u308b\u305f\u3081\u3001\u8ffd\u52a0\u3055\u308c\u306a\u3044\u3002<\/p>\n
INSERT IGNORE INTO users(name, email) VALUES('\u9234\u6728', 'suzuki2@example.com');\n\/\/ 0 row(s) affected, 1 warning(s): 1062 Duplicate entry '\u9234\u6728' for key 'name'\n<\/code><\/pre>\n
Laravel 5.8\u3067\u306finsertOrIgnore<\/a>\u304c\u8ffd\u52a0\u3055\u308c\u3001\u6b21\u306e\u3088\u3046\u306b\u66f8\u3051\u308b\u3002<\/p>\n
DB::table('users')->insertOrIgnore([\n    ['name' => '\u9234\u6728', 'email' => 'suzuki@example.com'],\n    ['name' => '\u7530\u4e2d', 'email' => 'tanaka@example.com'],\n]);\n<\/code><\/pre>\n
(\u53c2\u8003)Database: Query Builder – Laravel – The PHP Framework For Web Artisans<\/a><\/p>\n
\u306a\u304a\u3001INSERT IGNORE\u304c\u30e6\u30cb\u30fc\u30af\u5236\u7d04\u4ee5\u5916\u306e\u30a8\u30e9\u30fc\u3082\u7121\u8996\u3059\u308b\u3088\u3046\u306b\u306a\u308b\u306e\u3067\u3001\u6ce8\u610f\u304c\u5fc5\u8981\u3060\u3002<\/p>\n
\u6b21\u306eSQL\u306f\u30a8\u30e9\u30fc\u306b\u306a\u308a\u3001\u767b\u9332\u3055\u308c\u306a\u3044\u3002<\/p>\n
INSERT INTO users(id, name, email) VALUES('a', '\u6e21\u8fba', 'watanabe@example.com');\n\/\/ Error Code: 1366. Incorrect integer value: 'a' for column 'id' at row 1\n<\/code><\/pre>\n
\u6b21\u306eSQL\u306f\u30a8\u30e9\u30fc\u306b\u306a\u3089\u305a\u3001\u767b\u9332\u3055\u308c\u3066\u3057\u307e\u3046\u3002<\/p>\n
INSERT IGNORE INTO users(id, name, email) VALUES('a', '\u6e21\u8fba', 'watanabe@example.com');\n<\/code><\/pre>\n
INSERT \u301c SELECT \u301c WHERE NOT EXISTS(\u301c)<\/h2>\n
\u4f8b\u3048\u3070\u3001\u6b21\u306e\u3088\u3046\u306a\u30c6\u30fc\u30d6\u30eb\u304c\u3042\u308b\u3068\u3059\u308b\u3002
\nUNIQUE\u5236\u7d04\u304c\u306a\u304f\u3066\u3082\u6a5f\u80fd\u3059\u308b\u305f\u3081\u3001UNIQUE\u5236\u7d04\u3092\u3064\u3051\u3066\u3044\u306a\u3044\u3002<\/p>\n
CREATE TABLE users (\n    id INT AUTO_INCREMENT NOT NULL,\n    name VARCHAR(255),\n    email VARCHAR(255),\n    PRIMARY KEY(id)\n);\n<\/code><\/pre>\n
name\u304c’\u9234\u6728’\u306b\u4e00\u81f4\u3059\u308b\u30ec\u30b3\u30fc\u30c9\u304c\u306a\u3051\u308c\u3070\u3001\u767b\u9332\u3059\u308bSQL\u306f\u6b21\u306e\u3088\u3046\u306b\u306a\u308b\u3002<\/p>\n
INSERT INTO users(name, email) \nSELECT '\u9234\u6728', 'suzuki@example.com'\nFROM dual\nWHERE NOT EXISTS (\n    SELECT 1\n    FROM users\n    WHERE name = '\u9234\u6728'\n)\n<\/code><\/pre>\n
Laravel\u3067\u306f\u6b21\u306e\u3088\u3046\u306b\u66f8\u304f\u3002<\/p>\n
$select = DB::table(DB::raw('dual'))\n    ->select([\n        DB::raw('\u9234\u6728'),\n        DB::raw('suzuki@example.com')\n    ])\n    ->whereNotExists(function ($query) {\n        $query->select(DB::raw(1))\n            ->from('users')\n            ->where('name', '\u9234\u6728');\n    });\n$bindings = $select->getBindings();\n$insertQuery = 'INSERT INTO users (name, email)' . $select->toSql();\nDB::insert($insertQuery, $bindings);\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
INSERT\u69cb\u6587\u306bIGNORE\u30ad\u30fc\u30ef\u30fc\u30c9\u3092\u4f7f\u7528\u3059\u308b IGNORE\u30ad\u30fc\u30ef\u30fc\u30c9\u3092\u4f7f\u7528\u3059\u308b\u3068 UNIQUE\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u307e\u305f\u306fPRIMARY KEY\u5024\u3092\u8907\u88fd\u306b\u3059\u308b\u884c\u3088\u3063\u3066\u91cd\u8907\u30a8\u30e9\u30fc\u304c\u767a\u751f\u3057\u305f\u3068\u304d\u3001\u30b9\u30c6\u30fc\u30c8\u30e1\u30f3\u30c8\u306f\u4e2d\u6b62\u3055\u308c\u308b\u3002 \u91cd …<\/p>\n
Continue reading ‘MySQL\u3067\u6761\u4ef6\u306b\u4e00\u81f4\u3059\u308b\u30ec\u30b3\u30fc\u30c9\u304c\u306a\u3051\u308c\u3070\u65b0\u898f\u767b\u9332\u3001\u3042\u308c\u3070\u4f55\u3082\u3057\u306a\u3044’ »<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[25,139],"tags":[186,187,151,60],"class_list":["post-8510","post","type-post","status-publish","format-standard","hentry","category-php","category-sql","tag-laravel","tag-mysql","tag-php","tag-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.gesource.jp\/weblog\/index.php?rest_route=\/wp\/v2\/posts\/8510","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.gesource.jp\/weblog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.gesource.jp\/weblog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.gesource.jp\/weblog\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.gesource.jp\/weblog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=8510"}],"version-history":[{"count":0,"href":"https:\/\/www.gesource.jp\/weblog\/index.php?rest_route=\/wp\/v2\/posts\/8510\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.gesource.jp\/weblog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8510"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.gesource.jp\/weblog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8510"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.gesource.jp\/weblog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8510"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}