[B! sql][mysql] snjxã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯

snjx id:snjx

sqlã¨mysqlã«é–¢ã™ã‚‹snjxã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯ (56)

${{author_name}}$

{{{comment_expanded}}}

{{label}}

{{#is_bookmark}}ãƒªã‚¹ãƒˆ{{/is_bookmark}}{{^is_bookmark}}ãƒªãƒ³ã‚¯{{/is_bookmark}}

${{author_name}}$
{{author_name}}{{created}}
{{ #comment }}{{ comment }}{{ /comment }}
- {{ label }}

${{author_name}}$

{{{comment_expanded}}}

{{label}}

{{#is_bookmark}}ãƒªã‚¹ãƒˆ{{/is_bookmark}}{{^is_bookmark}}ãƒªãƒ³ã‚¯{{/is_bookmark}}

MySQLã§INSERTã®ãƒ‡ãƒƒãƒ‰ãƒãƒƒã‚¯ã«åµŒã‚‹äººã‚’1äººã§ã‚‚æ¸›ã‚‰ã™ãŸã‚ã« - ichirin2501's diary
ã“ã®è¨˜äº‹ã¯ã¯ã¦ãªãƒ‡ãƒ™ãƒãƒƒãƒ‘ãƒ¼ã‚¢ãƒ‰ãƒ™ãƒ³ãƒˆã‚«ãƒ¬ãƒ³ãƒ€ãƒ¼2015ã®12æœˆ24æ—¥ã®è¨˜äº‹ã§ã™ã€‚ æ˜¨æ—¥ã¯ id:stefafafan ã•ã‚“ã®ã‚¨ãƒ³ã‚¸ãƒ‹ã‚¢ã¨è‹±èªžã§ã—ãŸã€‚ ã“ã‚“ã«ã¡ã¯ã€ã“ã‚“ã°ã‚“ã¯ã€‚ ã‚¯ãƒªã‚¹ãƒžã‚¹ãƒ»ã‚¤ãƒ´ã§ã™ãã€çš†ã•ã‚“ã¯ã©ã®ã‚ˆã†ãªä¸€æ—¥ã‚’éŽã”ã•ã‚Œã‚‹(ãŸ)ã®ã§ã—ã‚‡ã†ã‹ã€‚ åƒ•ã¯ä¸€äººã§ã™ã€‚ æ”¹ã‚ã¾ã—ã¦ã€å…ˆæ—¥åˆã‚ã¦ã®åˆã‚³ãƒ³ã‚’çµŒé¨“/å¤±æ•—ã—ã¦äºŒåº¦ã¨è¡Œã‹ãªã„ã¨èª“ã£ãŸ ã¯ã¦ãªã® id:ichirin2501 ã§ã™ã€‚ä»Šå›žã¯å°ãƒã‚¿ã¨ã—ã¦MySQL(InnoDB)ã®BULK INSERTã«ãŠã‘ã‚‹ãƒ‡ãƒƒãƒ‰ãƒãƒƒã‚¯ã®è©±ã‚’ã—ã‚ˆã†ã¨æ€ã„ã¾ã™ã€‚ãŸã ã€å¤–éƒ¨ã‚ãƒ¼åˆ¶ç´„ãŒçµ¡ã‚€ã¨è¤‡é›‘ã«ãªã‚‹ã®ã§ä»Šå›žã¯è§¦ã‚Œã¾ã›ã‚“ã€‚ãã‚Œã«ã¤ã„ã¦ã¯ã“ã¡ã‚‰ã‚’å‚ç…§ãã ã•ã„ã€‚ ã‚ã€ã‚¿ã‚¤ãƒˆãƒ«ã¯ã‚ªãƒžãƒ¼ã‚¸ãƒ¥ã§ã™*1ã€‚ Topic æ¤œè¨¼ç’°å¢ƒ INSERTã®ãƒ‡ãƒƒãƒ‰ãƒãƒƒã‚¯ é¿ã‘ã‚‰ã‚Œãªã„ã‚±ãƒ¼ã‚¹ ã‚‚ã—ãã¯ãƒãƒƒã‚¯ã™ã‚‹ ãƒªãƒˆãƒ©ã‚¤å‡¦ç†ã«æ³¨æ„ åˆæœŸãƒ‡ãƒ¼ã‚¿ Duplicateã®å ´åˆ Deadlockã®
snjx 2023/02/03
mysql

db

ã‚¨ãƒ©ãƒ¼

sql

æŠ€è¡“

è€ƒå¯Ÿ
ãƒªãƒ³ã‚¯
MySQLã®ã‚¿ã‚¤ãƒ ã‚¢ã‚¦ãƒˆã‚¨ãƒ©ãƒ¼ï¼ˆ#1205 - Lock wait timeout exceededï¼‰ã®ç¢ºèªåŠã³å¯¾å‡¦ | å¤æ¾
MySQLã‚¨ãƒ©ãƒ¼ï¼š#1205 - Lock wait timeout exceeded; try restarting transaction mysql MySQLã®ã‚ã‚‹ãƒ†ãƒ¼ãƒ–ãƒ«ã«ãƒ‡ãƒ¼ã‚¿ç§»è¡Œï¼ˆinsertï¼‰æ™‚ã«ã‚¨ãƒ©ãƒ¼ãŒç™ºç”Ÿã—ã¾ã—ãŸ #1205 - Lock wait timeout exceeded; try restarting transaction mysql ã‚¨ãƒ©ãƒ¼å¾Œã«ã€Œtry restarting transactionã€ã§ã€MySQLãŒãƒ‡ãƒ¼ã‚¿è¿½åŠ ãƒ—ãƒã‚»ã‚¹ã‚’å†èµ·å‹•ã—ãŸã‚ˆã†ã§ã™ ãƒ‡ãƒ¼ã‚¿ç§»è¡ŒSQLæ–‡ï¼ˆéžå¸¸ã«å˜ç´”ãªãƒ†ãƒ¼ãƒ–ãƒ«é–“ã®ãƒ‡ãƒ¼ã‚¿ç§»å‹•ï¼‰ insert into drills_ch_sentence_temp select * from drills_ch_sentence åŽŸå› ï¼šä¸æ˜Žï¼ˆãŠãã‚‰ããƒ‡ãƒ¼ã‚¿é‡ãŒå¤šã„ï¼‰ ã¯ã£ãã‚Šã—ãŸç†ç”±ã¯åˆ†ã‹ã‚Šã¾ã›ã‚“ ãŠãã‚‰ããƒ‡ãƒ¼ã‚¿é‡ãŒå¤šã„ã€ãƒ†ãƒ¼ãƒ–ãƒ«ä½œæˆæ™‚ã«
snjx 2022/06/07
æŠ€è¡“

mysql

db

sql
ãƒªãƒ³ã‚¯
transactionã®é€”ä¸ã§ãƒˆãƒ©ãƒ³ã‚¶ã‚¯ã‚·ãƒ§ãƒ³ãŒåˆ‡ã‚Œã¦ã—ã¾ã£ãŸæ™‚ã«ãã®ãƒˆãƒ©ãƒ³ã‚¶ã‚¯ã‚·ãƒ§ãƒ³ã‚’æ®ºã™æ–¹æ³• - Garbage in, gospel out
èµ·ã“ã£ãŸã“ã¨ transactionãƒ¢ãƒ¼ãƒ‰ã§ä½œæ¥é€”ä¸ã€ã‚»ãƒƒã‚·ãƒ§ãƒ³ãŒåˆ‡ã‚ŒãŸã€‚ ãã®ã¨ãç‰¹å®štableã«insertã‚’ã—ãŸãŒcommitã—ã¦ã„ãªã„çŠ¶æ…‹ã ã£ãŸã€‚ å¾Œã»ã©å†åº¦ãã®tableã‚’selectã—ãŸãŒã€å½“ç„¶commitã—ã¦ã„ãªã„ã®ã§ãƒ‡ãƒ¼ã‚¿ãŒãªã‹ã£ãŸã€‚ ã—ã‹ã—insertã—ã‚ˆã†ã¨ã™ã‚‹ã¨ã€ ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction ã¨ã„ã†ã‚¨ãƒ©ãƒ¼ãŒè¡¨ç¤ºã•ã‚Œå¤±æ•—ã—ãŸã€‚ åŽŸå› transactionãŒãƒãƒƒã‚¯ã‚’å–å¾—ã—ãŸã¾ã¾æ”¾ç½®çŠ¶æ…‹ã«ãªã£ã¦ã„ãŸãŸã‚ã«ç™ºç”Ÿã—ãŸã€‚ å¯¾å‡¦ åŸºæœ¬æ–¹é‡ã¯ show processlist; ã¨ show innodb status; ã®çµæžœã‚’è¦‹æ¯”ã¹ã¦ãã‚Œã£ã½ã„threadè¦‹ã¤ã‘ã¦ kill [thread id] ã™ã‚‹ã€‚ show processlist; ã—ã¦ã¿ã‚‹ã¨ã€ä»Šå›ž
snjx 2022/06/07
æŠ€è¡“

mysql

db

sql
ãƒªãƒ³ã‚¯
ãƒˆãƒ©ãƒ³ã‚¶ã‚¯ã‚·ãƒ§ãƒ³ã‚’å¼·åˆ¶çš„ã«çµ‚äº†ã•ã›ã‚‹ - HHeLiBeXã®æ—¥è¨˜ æ£é“ç·¨
å‰ç½®ã MySQL 5.1ã§ã€InnoDBãªãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã‚’ä½¿ã£ã¦ä½•ã‹ã‚’é–‹ç™ºã—ã¦ã„ã‚‹æœ€ä¸ã«ã¯ã€ãƒˆãƒ©ãƒ³ã‚¶ã‚¯ã‚·ãƒ§ãƒ³ãŒã‚³ãƒŸãƒƒãƒˆ(ã¾ãŸã¯ãƒãƒ¼ãƒ«ãƒãƒƒã‚¯)ã•ã‚Œã‚‹å‰ã«ãƒ—ãƒã‚°ãƒ©ãƒ å´ã®å‡¦ç†ãŒ(Fatal errorãªã©ã§)ç•°å¸¸çµ‚äº†ã—ãŸã‚Šã—ã¦ã€ãƒˆãƒ©ãƒ³ã‚¶ã‚¯ã‚·ãƒ§ãƒ³ãŒãƒãƒƒã‚¯ã‚’å–å¾—ã—ãŸã¾ã¾æ”¾ç½®çŠ¶æ…‹ã«ãªã£ã¦ã—ã¾ã†ã“ã¨ãŒã‚ˆãã‚ã‚‹ã‚‰ã—ã„(è¬Ž)ã€‚ ãã‚“ãªæ™‚ã€MySQLã®ã‚³ãƒžãƒ³ãƒ‰ãƒ©ã‚¤ãƒ³ã‹ã‚‰æ›´æ–°ã‚¯ã‚¨ãƒªã‚’ç™ºè¡Œã™ã‚‹ã¨ã€ã—ã°ã‚‰ãå¾Œã«æ¬¡ã®ã‚ˆã†ãªã‚¨ãƒ©ãƒ¼ãŒè¿”ã£ã¦ãã‚‹ã“ã¨ãŒã‚ã‚‹ã€‚ ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction ä¸€å¿œå‚è€ƒã¾ã§ã«æ›¸ã„ã¦ãŠãã¨ã€ã“ã®ã‚¨ãƒ©ãƒ¼ãŒè¿”ã£ã¦ãã‚‹ã¾ã§ã®æ™‚é–“ã¯ã€æ¬¡ã®ã‚³ãƒžãƒ³ãƒ‰ã§çŸ¥ã‚‹ã“ã¨ãŒã§ãã‚‹ã€‚ mysql> show global variables like 'innodb_lock_wait_timeout';
snjx 2022/06/07
æŠ€è¡“

mysql

db

sql
ãƒªãƒ³ã‚¯
Mysqlã§ãƒã‚°ç³»ãƒ†ãƒ¼ãƒ–ãƒ«ã‚’é‹ç”¨ã™ã‚‹ã¨ãã‚„ã£ã¦ãŠããŸã„ã“ã¨ - ä¸»å¤«ã¨ãã©ããƒ—ãƒã‚°ãƒ©ãƒž
SNSã‚„ã‚½ãƒ¼ã‚·ãƒ£ãƒ«ã‚²ãƒ¼ãƒ ã€ã‚¢ãƒ‰ãƒãƒƒãƒˆãƒ¯ãƒ¼ã‚¯ãªã©ã®ã‚·ã‚¹ãƒ†ãƒ ã§ã¯ã„ã‚ã„ã‚ãªãƒã‚°æƒ…å ±ã‚’DBã«ä¿å˜ã™ã‚‹ã“ã¨ã‚‚ã‚ã‚‹ã¨æ€ã„ã¾ã™ã€‚ ãã®ã•ã„ã€æ—¥ã€…å¢—ãˆã¤ã¥ã‘ã‚‹ãƒ‡ãƒ¼ã‚¿ã‚„ãƒ‘ãƒ•ã‚©ãƒ¼ãƒžãƒ³ã‚¹ã‚’ã©ã®æ§˜ã«ã•ã°ã„ã¦ã„ãã‹ãŒé‡è¦ã«ãªã£ã¦ãã¾ã™ã€‚ ä»Šå›žã¯ãƒã‚°ç³»ã®ãƒ‡ãƒ¼ã‚¿ã‚’Mysqlã§ã©ã®ã‚ˆã†ã«é‹ç”¨ã—ã¦ã„ãã‹ã€ã‚’ãƒ†ãƒ¼ãƒžã«ã„ãã¤ã‹ã®ãƒŽã‚¦ãƒã‚¦ã‚’ã¾ã¨ã‚ã¾ã—ãŸã€‚ ãƒã‚°ç³»ãƒ†ãƒ¼ãƒ–ãƒ«ã®ç‰¹å¾´ ãƒã‚°ç³»ã®ãƒ‡ãƒ¼ã‚¿ã¨ã¯ã€ã¤ã¾ã‚Šä½•ã‹ã®ã‚¢ã‚¯ã‚·ãƒ§ãƒ³ã®å±¥æ´ãƒ‡ãƒ¼ã‚¿ã®ã“ã¨ã§ã™ã€‚ ä¸€èˆ¬çš„ã«ã¯ã“ã®ã‚ˆã†ãªå½¢ã«ãªã‚‹ã‹ã¨æ€ã„ã¾ã™ã€‚ CREATE TABLE `t_logs` ( `id` bigint(20) unsigned NOT NULL, `user_id` int(10) unsigned NOT NULL DEFAULT '0', `event_id` int(10) unsigned NOT NULL DEFAULT '0', `created` datet
snjx 2021/12/27
ã‚ã¨ã§èªã‚€

mysql

é‹ç”¨

ãƒ‡ãƒ¼ã‚¿

db

è¨è¨ˆ

ãƒ‘ãƒ•ã‚©ãƒ¼ãƒžãƒ³ã‚¹

è¨å®š

sql

æŠ€è¡“
ãƒªãƒ³ã‚¯
MySQLã®ãƒ‡ãƒ¼ã‚¿ã‚’csv,tsvå½¢å¼ã§ãƒ€ãƒ³ãƒ—ã™ã‚‹ - Qiita
MySQLã«ã¯ mysqldumpã¨ã„ã†ãƒ€ãƒ³ãƒ—ãƒ„ãƒ¼ãƒ«ãŒã‚ã‚Šã¾ã™ã€‚ åŸºæœ¬çš„ãªä½¿ã„æ–¹ã¯ä»¥ä¸‹ã®ã‚ˆã†ã«ãªã‚Šã¾ã™ã€‚ å‡ºåŠ›å½¢å¼ã¯SQLã®CREATEæ–‡ã¨INSERTæ–‡ã«ãªã‚Šã¾ã™ãŒã€ã‚ªãƒ—ã‚·ãƒ§ãƒ³ã‚’æŒ‡å®šã™ã‚‹ã“ã¨ã§ã€ csvã‚„ tsvå½¢å¼ã«ã™ã‚‹ã“ã¨ãŒå¯èƒ½ã§ã™ã€‚ csv,tsvå‡ºåŠ›ã®ã‚ªãƒ—ã‚·ãƒ§ãƒ³ csv(tsv)ã§å‡ºåŠ›ã™ã‚‹ã«ã¯ã€ --tabã¨ --fields-terminated-by ã®2ã¤ã®ã‚ªãƒ—ã‚·ãƒ§ãƒ³ã‚’ä½¿ç”¨ã—ã¾ã™ã€‚ --tabã‚ªãƒ—ã‚·ãƒ§ãƒ³ ãƒ•ã‚¡ã‚¤ãƒ«ã®å‡ºåŠ›å…ˆãƒ‡ã‚£ãƒ¬ã‚¯ãƒˆãƒªã‚’æŒ‡å®šã™ã‚‹ã‚ªãƒ—ã‚·ãƒ§ãƒ³ã§ã™ã€‚ --tab=/tmpã¨æŒ‡å®šã™ã‚Œã°ã€/tmpä»¥ä¸‹ã«ãƒ•ã‚¡ã‚¤ãƒ«ãŒå‡ºåŠ›ã•ã‚Œã¾ã™ã€‚ ã“ã®ã‚ªãƒ—ã‚·ãƒ§ãƒ³ã¯ã€csv(tsv)å½¢å¼ã§å‡ºåŠ›ã™ã‚‹å ´åˆã¯å¿…é ˆã§ã™ã€‚ --fields-terminated-byã‚ªãƒ—ã‚·ãƒ§ãƒ³ åŒºåˆ‡ã‚Šæ–‡å—ã‚’æŒ‡å®šã™ã‚‹ã‚ªãƒ—ã‚·ãƒ§ãƒ³ã§ã™ã€‚ ãƒ‡ãƒ•ã‚©ãƒ«ãƒˆã¯ã‚¿ãƒ–ã«ãªã‚Šã¾ã™ã®ã§ã€tsvãƒ•ã‚¡ã‚¤ãƒ«ã§å‡ºåŠ›ã™ã‚‹å ´åˆã¯ã“ã®ã‚ªãƒ—ã‚·ãƒ§ãƒ³ã¯ä¸è¦ã§ã™
snjx 2021/11/09
mysql

ã‚·ã‚¹ãƒ†ãƒ é–‹ç™º

æŠ€è¡“

db

sql

CSV
ãƒªãƒ³ã‚¯
[MySQL] ãƒ†ãƒ¼ãƒ–ãƒ«ã‚’ã‚³ãƒ”ãƒ¼ã™ã‚‹ - ãã“ã®è¶³è·¡R
MySQLã§ãƒ†ãƒ¼ãƒ–ãƒ«ã‚’ã‚³ãƒ”ãƒ¼ã—ã¾ã™ã€‚ ä»¥ä¸‹ã®2ã¤ã®ãƒ‘ã‚¿ãƒ¼ãƒ³ã®ãã‚Œãžã‚Œã®ç‰¹å¾´ã«ã¤ã„ã¦ãƒ¡ãƒ¢ã—ã¾ã™ã€‚ ãƒ‘ã‚¿ãƒ¼ãƒ³1 CREATE TABLE newtable LIKE oldtable; INSERT INTO newtable SELECT * FROM oldtable; ãƒ‘ã‚¿ãƒ¼ãƒ³2 CREATE TABLE newtable SELECT * FROM oldtable; ãƒ†ãƒ¼ãƒ–ãƒ«ã‚’ã‚³ãƒ”ãƒ¼ CREATE TABLE ... LIKE æ§‹æ–‡ æ§‹æ–‡ è©¦ã—ã¦ã¿ã‚‹ CREATE TABLE ... SELECT æ§‹æ–‡ æ§‹æ–‡ è©¦ã—ã¦ã¿ã‚‹ å‚è€ƒãƒšãƒ¼ã‚¸ ãƒ†ãƒ¼ãƒ–ãƒ«ã‚’ã‚³ãƒ”ãƒ¼ MySQLã§ã¯å¤§ãã2ã¤ã®æ–¹æ³•ãŒã‚ã‚Šã€ãã‚Œãžã‚Œç‰¹å¾´ãŒã‚ã‚Šã¾ã™ã€‚ ï¼‘ã¤ãšã¤è©¦ã—ã¦ã¿ãŸã„ã¨æ€ã„ã¾ã™ã€‚ ä»Šå›žã¯ã‚³ãƒ”ãƒ¼å…ƒã®ãƒ†ãƒ¼ãƒ–ãƒ«ã¨ã—ã¦ä»¥ä¸‹ã®ãƒ†ãƒ¼ãƒ–ãƒ«ã‚’ç”¨æ„ã—ã¾ã—ãŸã€‚ /*-- ã‚³ãƒ”ãƒ¼å…ƒã®ãƒ†ãƒ¼ãƒ–ãƒ« --*/ CREATE TABLE foo(
snjx 2021/10/07
â€œCREATE TABLE newtable SELECT * FROM oldtable;â€

mysql

sql

æŠ€è¡“

db
ãƒªãƒ³ã‚¯
é‡è¤‡ã—ã¦ã„ã‚‹ãƒ¬ã‚³ãƒ¼ãƒ‰ã‚’æ¤œç´¢ã™ã‚‹SQL(å¤§é‡ãƒ‡ãƒ¼ã‚¿ã‚‚å¯¾å¿œ) - Qiita
Deleted articles cannot be recovered. Draft of this article would be also deleted. Are you sure you want to delete this article?
snjx 2021/10/04
æ¤œç´¢

sql

ãƒ‡ãƒ¼ã‚¿

mysql

DB

æŠ€è¡“
ãƒªãƒ³ã‚¯
MySQL 8.0 ã¸ã®ç§»è¡ŒãŒå®Œäº†ã—ã¾ã—ãŸ ï½žã•ã‚ˆã†ãªã‚‰å…¨ã¦ã® MySQL 5.7ï½ž - Cybozu Inside Out | ã‚µã‚¤ãƒœã‚¦ã‚ºã‚¨ãƒ³ã‚¸ãƒ‹ã‚¢ã®ãƒ–ãƒã‚°
ã“ã‚“ã«ã¡ã¯ã€‚ã‚¯ãƒ©ã‚¦ãƒ‰é‹ç”¨ãƒãƒ¼ãƒ ã®é£¯å¡šã§ã™ã€‚ ç§ãŸã¡ã¯ cybozu.com æœ¬ç•ªç’°å¢ƒã® MySQL ã‚’æ˜¨å¹´æœ«ã‹ã‚‰é †æ¬¡ 8.0 ç³»ã¸ã‚¢ãƒƒãƒ—ã‚°ãƒ¬ãƒ¼ãƒ‰ã—ã¦ãŠã‚Šã€å‰å›žã®å®šæœŸãƒ¡ãƒ³ãƒ†ãƒŠãƒ³ã‚¹ã«ãŠã„ã¦å…¨ã¦ã®ã‚¤ãƒ³ã‚¹ã‚¿ãƒ³ã‚¹ã®ã‚¢ãƒƒãƒ—ã‚°ãƒ¬ãƒ¼ãƒ‰ã‚’å®Œäº†ã—ã¾ã—ãŸã€‚ã“ã®è¨˜äº‹ã§ã¯ã€ç§ãŸã¡ãŒ MySQL 8.0 ã¸ã®ç§»è¡Œã«å–ã‚Šçµ„ã‚“ã ç†ç”±ã¨å¿…è¦ã«ãªã£ãŸå¯¾å¿œã«ã¤ã„ã¦ç´¹ä»‹ã—ã¾ã™ã€‚ ãªãœ MySQL 8.0 ã¸ç§»è¡Œã—ãŸã®ã‹ GTID-based ãƒ¬ãƒ—ãƒªã‚±ãƒ¼ã‚·ãƒ§ãƒ³ã«ãŠã‘ã‚‹åˆ¶é™ã®ç·©å’Œ å†èµ·å‹•æ™‚ã« AUTO_INCREMENT ã®ã‚«ã‚¦ãƒ³ã‚¿ãŒå·»ãæˆ»ã‚‹å•é¡Œã®è§£æ¶ˆ å®Ÿéš›ã«å¯¾å¿œãŒå¿…è¦ã ã£ãŸ MySQL 8.0 ã®å¤‰æ›´ç‚¹ utf8mb4 ã®ç…§åˆé †åºã®ãƒ‡ãƒ•ã‚©ãƒ«ãƒˆå€¤ã®å¤‰æ›´ SQL_CALC_FOUND_ROWS ã¨ FOUND_ROWS() ãŒ deprecated ã« Connector/J ã®ãƒ¡ã‚¿ãƒ‡ãƒ¼ã‚¿å–å¾—å‡¦ç†ã®æ€§èƒ½ä½Žä¸‹ sys.innodb_lo
snjx 2021/05/24
mysql

æŠ€è¡“

sql

è€ƒå¯Ÿ

é–‹ç™ºç’°å¢ƒ

ç’°å¢ƒæ§‹ç¯‰
ãƒªãƒ³ã‚¯
MySQLã§å…¨ãƒ†ãƒ¼ãƒ–ãƒ«ã®ãƒ¬ã‚³ãƒ¼ãƒ‰æ•°ã‚’ä¸€è¦§è¡¨ç¤ºã™ã‚‹ - Qiita
mysql> select table_name, table_rows from information_schema.TABLES where table_schema = 'DBå'; mysql> select table_name, table_rows from information_schema.TABLES where table_schema = 'zabbix'; +----------------------------+------------+ | table_name | table_rows | +----------------------------+------------+ | acknowledges | 15 | | actions | 33 | | alerts | 4104 | | application_discovery | 0 | |
snjx 2021/03/03
â€œselect table_name, table_rows from information_schema.TABLES where table_schema = 'DBå';â€

mysql

db

sql

æŠ€è¡“
ãƒªãƒ³ã‚¯
MySQL :: MySQL 8.0 ãƒªãƒ•ã‚¡ãƒ¬ãƒ³ã‚¹ãƒžãƒ‹ãƒ¥ã‚¢ãƒ« :: B.3.2.8 ãƒ‘ã‚±ãƒƒãƒˆãŒå¤§ãã™ãŽã¾ã™
é€šä¿¡ãƒ‘ã‚±ãƒƒãƒˆã¯ã€MySQL ã‚µãƒ¼ãƒãƒ¼ã«é€ä¿¡ã•ã‚Œã‚‹å˜ä¸€ã® SQL ã‚¹ãƒ†ãƒ¼ãƒˆãƒ¡ãƒ³ãƒˆã€ã‚¯ãƒ©ã‚¤ã‚¢ãƒ³ãƒˆã«é€ä¿¡ã•ã‚Œã‚‹å˜ä¸€è¡Œã€ã¾ãŸã¯ã‚½ãƒ¼ã‚¹ãƒ¬ãƒ—ãƒªã‚±ãƒ¼ã‚·ãƒ§ãƒ³ã‚µãƒ¼ãƒãƒ¼ã‹ã‚‰ãƒ¬ãƒ—ãƒªã‚«ã«é€ä¿¡ã•ã‚Œã‚‹ãƒã‚¤ãƒŠãƒªãƒã‚°ã‚¤ãƒ™ãƒ³ãƒˆã§ã™ã€‚ MySQL 8.0 Server ãŠã‚ˆã³ã‚¯ãƒ©ã‚¤ã‚¢ãƒ³ãƒˆé–“ã§è»¢é€å¯èƒ½ãªãƒ‘ã‚±ãƒƒãƒˆã®æœ€å¤§ã‚µã‚¤ã‚ºã¯ 1G ãƒã‚¤ãƒˆã§ã™ã€‚ MySQL ã‚¯ãƒ©ã‚¤ã‚¢ãƒ³ãƒˆã¾ãŸã¯ mysqld ã‚µãƒ¼ãƒãƒ¼ãŒ max_allowed_packet ãƒã‚¤ãƒˆã‚ˆã‚Šå¤§ãã„ãƒ‘ã‚±ãƒƒãƒˆã‚’å—ã‘å–ã‚‹ã¨ã€ER_NET_PACKET_TOO_LARGE ã‚¨ãƒ©ãƒ¼ãŒç™ºè¡Œã•ã‚Œã€æŽ¥ç¶šãŒå¤±ã‚ã‚Œã¾ã™ã€‚ ä¸€éƒ¨ã®ã‚¯ãƒ©ã‚¤ã‚¢ãƒ³ãƒˆã§ã¯ã€ãƒ‘ã‚±ãƒƒãƒˆãŒå¤§ãã™ãŽã‚‹å ´åˆã€ã€Œã‚¯ã‚¨ãƒªãƒ¼ä¸ã« MySQL ã‚µãƒ¼ãƒãƒ¼ã¸ã®æŽ¥ç¶šãŒå¤±ã‚ã‚Œã¾ã—ãŸã€ã¨ã„ã†ã‚¨ãƒ©ãƒ¼ã‚’å—ã‘å–ã‚‹ã“ã¨ã‚‚ã‚ã‚Šã¾ã™ã€‚ ã‚¯ãƒ©ã‚¤ã‚¢ãƒ³ãƒˆã¨ã‚µãƒ¼ãƒãƒ¼ã®ä¸¡æ–¹ã«ãã‚Œãžã‚Œ max_allowed_packet å¤‰æ•°ãŒã‚ã‚‹ãŸã‚ã€å¤§ããªãƒ‘ã‚±ãƒƒãƒˆã‚’å‡¦ç†ã™ã‚‹å ´
snjx 2021/03/03
â€œ--max_allowed_packet=16Mâ€ è¨å®šä¸€å€‹ã ã‘å¤‰æ›´ã—ãŸã„ãªã‚‰ã€my.confã‚’ç”¨æ„ã™ã‚‹ã‚ˆã‚Šã¯ã‚³ãƒžãƒ³ãƒ‰ãƒ©ã‚¤ãƒ³æ›¸ã„ãŸã»ã†ãŒæ—©ã„

mysql

db

sql

ç’°å¢ƒæ§‹ç¯‰

æŠ€è¡“
ãƒªãƒ³ã‚¯
MySQLã§ãƒ†ãƒ¼ãƒ–ãƒ«ã®æ›´æ–°æ—¥æ™‚ã‚’è¡¨ç¤ºã™ã‚‹ | ã‚¯ãƒã‚¸ã‚«
ãƒ›ãƒ¼ãƒ / ãƒãƒƒã‚¯ / MySQLã§ãƒ†ãƒ¼ãƒ–ãƒ«ã®æ›´æ–°æ—¥æ™‚ã‚’è¡¨ç¤ºã™ã‚‹
snjx 2021/03/01
mysql

æŠ€è¡“

db

sql
ãƒªãƒ³ã‚¯
ã€MySQLã€‘å¤–éƒ¨ã‚ãƒ¼åˆ¶ç´„ã‚’ä¸€æ™‚çš„ã«ç„¡åŠ¹ã«ã™ã‚‹ - Qiita
Deleted articles cannot be recovered. Draft of this article would be also deleted. Are you sure you want to delete this article?
snjx 2021/02/10
æ€–ã„æ€–ã„

mysql

db

sql

æŠ€è¡“
ãƒªãƒ³ã‚¯
ã€Ruby on Railsã€‘é‡è¤‡ã—ã¦ã„ã‚‹ãƒ¬ã‚³ãƒ¼ãƒ‰ã‚’å–å¾—ã™ã‚‹ã€MySQLã€‘ - ãã‚ƒã¾ãªã‹ã®ãƒ–ãƒã‚°
æ¦‚è¦ ã¯ã˜ã‚ã« é‡è¤‡ã—ã¦ã„ã‚‹ãƒ¬ã‚³ãƒ¼ãƒ‰ã‚’å–å¾— MySQL ã®å ´åˆ Ruby on Rails ã®å ´åˆ é‡è¤‡ã—ã¦ã„ã‚‹ãƒ¬ã‚³ãƒ¼ãƒ‰ã‚’å…¨ã¦å–å¾— MySQL ã®å ´åˆ Ruby on Rails ã®å ´åˆ ãŠã¾ã‘ æ¡ä»¶ã‚’æº€ãŸã•ãªã„é‡è¤‡ãƒ¬ã‚³ãƒ¼ãƒ‰ã‚’å‰Šé™¤ã™ã‚‹ MySQL ã®å ´åˆ Ruby on Rails ã®å ´åˆ ã¾ã¨ã‚ æ¦‚è¦ Ruby on Rails ã§ DB ã‹ã‚‰é‡è¤‡ã—ã¦ã„ã‚‹ãƒ¬ã‚³ãƒ¼ãƒ‰ã‚’å–å¾—ã—ãŸã„æ™‚ãŒã‚ã‚Šã¾ã—ãŸã€‚ MySQL ã§é‡è¤‡ã—ã¦ã„ã‚‹ãƒ¬ã‚³ãƒ¼ãƒ‰ã‚’å–å¾—ã™ã‚‹æ–¹æ³•ã¯åˆ†ã‹ã‚‹ã®ã§ã™ãŒã€Ruby on Rails ã§åŒã˜å‡¦ç†ã‚’æ›¸ãå ´åˆã€ã©ã†ã—ãŸã‚‰è‰¯ã„ã®ã‹åˆ†ã‹ã‚‰ãªã‹ã£ãŸã®ã§èª¿ã¹ã¦è¦‹ã¾ã—ãŸã€‚ MySQL ã§æ›¸ãå ´åˆã¨ã€Ruby on Rails ã§æ›¸ãå ´åˆã® 2 ãƒ‘ã‚¿ãƒ¼ãƒ³ç”¨æ„ã—ã¦ã„ã¾ã™ã€‚ ã¯ã˜ã‚ã« ä»Šå›žåˆ©ç”¨ã™ã‚‹ãƒ†ãƒ¼ãƒ–ãƒ«ã®ä¸èº«ã‚’ç¢ºèªã—ã¾ã™ã€‚ ãƒ¦ãƒ¼ã‚¶å aaa ãŒ 3 ä»¶é‡è¤‡ã€bbb ãŒ 2 ä»¶é‡è¤‡ã€ccc ãŒé‡è¤‡ç„¡ã—ã¨ãªã£
snjx 2020/01/24
ruby

rails

DB

æŠ€è¡“

mysql

sql

è€ƒå¯Ÿ
ãƒªãƒ³ã‚¯
mysqldumpã§Couldnâ€™t executeã¨è¨€ã‚ã‚ŒãŸæ™‚ã®å¯¾ç– â€“ å¡©ç„¼ããƒ–ãƒã‚°
ä¸‹è¨˜ã®ã‚ˆã†ãªmysqldumpã‚³ãƒžãƒ³ãƒ‰ã‚’å®Ÿè¡Œã—ãŸã¨ã“ã‚ mysqldump -h database -u hoge hoge2 -phoge3 --skip-lock-tables --default-character-set=utf8mb4 hoge4 hoge5 > ~/Downloads/hoge6.sql ä¸‹è¨˜ã®ã‚ˆã†ãªã‚¨ãƒ©ãƒ¼ãŒå‡ºãŸ mysqldump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"') FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = 'hoges' AND TABLE_NAME = 'it em';': Unknown table 'COLUMN_S
snjx 2019/12/03
æŠ€è¡“

sql

database

ã‚³ãƒžãƒ³ãƒ‰

mysql
ãƒªãƒ³ã‚¯
MySQLã®AUTO_INCREMENTã«ã¤ã„ã¦è‰²ã€…ã¨èª¿ã¹ã¦ã¿ãŸã€‚ - Qiita
AUTO_INCREMENTã¨ã¯ ã‚«ãƒ©ãƒ ã«å€¤ãŒæŒ‡å®šã•ã‚Œãªã‹ã£ãŸå ´åˆã€MySQLãŒè‡ªå‹•çš„ã«å€¤ã‚’å‰²ã‚Šå½“ã¦ã‚‹ã€‚ ãƒ‡ãƒ¼ã‚¿åž‹ã¯æ•´æ•°ã€‚ å€¤ã¯1ãšã¤å¢—åŠ ã—ã¦é€£ç•ªã«ãªã‚‹ã€‚ åŸºæœ¬çš„ãªã“ã¨ã€‚ ãƒ†ã‚¹ãƒˆãƒ†ãƒ¼ãƒ–ãƒ«ä½œæˆ mysql> CREATE TABLE fruit(id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, name VARCHAR(50), price INT); mysql> DESC fruit; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | in
snjx 2019/11/06
mysql

db

sql

æŠ€è¡“

è€ƒå¯Ÿ
ãƒªãƒ³ã‚¯
MySQLã§å¤–éƒ¨ã‚ãƒ¼ã®åˆ¶ç´„ãŒã‚ã‚‹ãƒ†ãƒ¼ãƒ–ãƒ«ã‚’truncateã™ã‚‹æ–¹æ³• | ãƒãƒƒã‚¯ãƒŽãƒ¼ãƒˆ
ãƒ›ãƒ¼ãƒ / ãƒãƒƒã‚¯ / MySQLã§å¤–éƒ¨ã‚ãƒ¼ã®åˆ¶ç´„ãŒã‚ã‚‹ãƒ†ãƒ¼ãƒ–ãƒ«ã‚’truncateã™ã‚‹æ–¹æ³•
snjx 2019/11/03
mysql

sql

æŠ€è¡“

ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹
ãƒªãƒ³ã‚¯
MySQL 8.0.16 ã§ãƒ†ãƒ¼ãƒ–ãƒ«ã‚¹ãƒšãƒ¼ã‚¹ãƒ»REDO ãƒã‚°ï¼UNDO ãƒã‚°ãƒ»ã‚·ã‚¹ãƒ†ãƒ ãƒ†ãƒ¼ãƒ–ãƒ«æš—å·åŒ– - Qiita
ä»¥å‰ MySQL 8.0.4 RCã§ãƒ†ãƒ¼ãƒ–ãƒ«ã‚¹ãƒšãƒ¼ã‚¹æš—å·åŒ–ï¼‹REDOãƒã‚°ï¼UNDOãƒã‚°æš—å·åŒ– ã¨ã„ã†è¨˜äº‹ã‚’æ›¸ãã¾ã—ãŸãŒã€ ãƒžã‚¤ãƒŠãƒ¼ãƒãƒ¼ã‚¸ãƒ§ãƒ³ãŒä¸ŠãŒã‚‹ã«ã¤ã‚Œã¦å¿…é ˆè¨å®šé …ç›®ãŒæ¸›ã£ãŸã“ã¨ MySQL 8.0.16 ã§ã‚·ã‚¹ãƒ†ãƒ ãƒ†ãƒ¼ãƒ–ãƒ«ï¼ˆmysqlã‚¹ã‚ãƒ¼ãƒžï¼‰ã®æš—å·åŒ–ã‚’ã‚µãƒãƒ¼ãƒˆã—ãŸã“ã¨ ã‹ã‚‰ã€ä»Šå›žã€8.0.16 ã‚’ãƒ™ãƒ¼ã‚¹ã«æ›¸ãç›´ã—ã¾ã—ãŸã€‚ ã€é–¢é€£è¨˜äº‹ã€‘ MySQL 8.0.13 ã§ä¸€èˆ¬ãƒ†ãƒ¼ãƒ–ãƒ«ã‚¹ãƒšãƒ¼ã‚¹ï¼ˆGeneral Tablespaceï¼‰ã‚’æš—å·åŒ–ã™ã‚‹ MySQL 8.0.14 ã§ãƒã‚¤ãƒŠãƒªãƒã‚°ãƒ»ãƒªãƒ¬ãƒ¼ãƒã‚°ã‚’æš—å·åŒ–ã™ã‚‹ ã€å‚è€ƒã€‘ 15.6.3.9 InnoDB Data-at-Rest Encryptionï¼ˆMySQL 8.0 Reference Manualï¼‰ è¨å®š UNDO ãƒã‚°ã®ç´°ã‹ã„è¨å®šã¯å¿…é ˆã§ã¯ãªããªã£ã¦ã„ã¾ã™ã€‚ ã¾ãŸã€innodb_file_per_table=1ãŒãƒ‡ãƒ•ã‚©ãƒ«ãƒˆè¨å®šã«å¤‰ã‚ã£ãŸãŸ
snjx 2019/10/23
mysql

ãƒ‡ãƒ¼ã‚¿

sql

æš—å·åŒ–

ã‚»ã‚ãƒ¥ãƒªãƒ†ã‚£

æŠ€è¡“

mysql8.0
ãƒªãƒ³ã‚¯
ActiveRecordã§æ™®é€šã®Enumåž‹ã‚«ãƒ©ãƒ ã‚’ä½œæˆã™ã‚‹ - Qiita
ã¯ã˜ã‚ã« ActiveRecordã§Enumã‚«ãƒ©ãƒ ã‚’å®Ÿç¾ã—ã‚ˆã†ã¨ã™ã‚‹ã¨ã€ä¸€èˆ¬çš„ã«ã¯ãƒ¢ãƒ‡ãƒ«ã«ã‚·ãƒ³ãƒœãƒ«é…åˆ—ã‚’å®šç¾©ã—ã¦ã€ã‚«ãƒ©ãƒ è‡ªä½“ã¯æ•°å€¤åž‹ã«ã™ã‚‹å ´åˆãŒå¤šã„ã‚ˆã†ã§ã™ã€‚ã—ã‹ã—ã€ã“ã®ã‚„ã‚Šæ–¹ã§ã¯ä»¥ä¸‹ã®å•é¡ŒãŒç™ºç”Ÿã—ã¾ã™ã€‚ å¯èªæ€§ãŒè‘—ã—ãè½ã¡ã‚‹ ãƒ„ãƒ¼ãƒ«ã‚’åˆ©ç”¨ã—ã¦ãƒ†ãƒ¼ãƒ–ãƒ«ã‚’é–²è¦§ã—ã¦ã‚‚ã€ã‚«ãƒ©ãƒ ã®å€¤ãŒä½•ã‚’æ„å‘³ã—ã¦ã„ã‚‹ã‹ã‚ã‹ã‚‰ãªã„ ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã‚’åˆ©ç”¨ã™ã‚‹ç‚ºã®æƒ…å ±ãŒã€ã‚¢ãƒ—ãƒªã‚±ãƒ¼ã‚·ãƒ§ãƒ³(ãƒ¢ãƒ‡ãƒ«)ã¨ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã«åˆ†é›¢ã•ã‚Œã¦ã—ã¾ã† ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ãŒã€ã“ã®ActiveRecordã‚’åˆ©ç”¨ã—ãŸã‚¢ãƒ—ãƒªã‚±ãƒ¼ã‚·ãƒ§ãƒ³ã«ä¾å˜ã—ã¦ã—ã¾ã† ç‰¹ã«ã€rubyã‚’ä½¿ã‚ãªã„ã‚¢ãƒ—ãƒªã‚±ãƒ¼ã‚·ãƒ§ãƒ³ã‹ã‚‰ã“ã®ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã‚’åˆ©ç”¨ã™ã‚‹éš›ã«å•é¡ŒãŒç”Ÿã˜ã‚‹ ä¸€èˆ¬ã«ã€ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã¯ã‚¢ãƒ—ãƒªã‚±ãƒ¼ã‚·ãƒ§ãƒ³ã‚ˆã‚Šå¯¿å‘½ãŒé•·ãã€ã¾ãŸæ˜¨ä»Šã§ã¯åŽé›†ã—ãŸãƒ‡ãƒ¼ã‚¿ã‚’pythonã§åˆ†æžã™ã‚‹ã“ã¨ã‚‚å°‘ãªããªã„ç‚ºã€ActiveRecordã«ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã®æ§‹é€ ã‚’å·¦å³ã•ã‚ŒãŸãã‚ã‚Šã¾ã›ã‚“ã€‚ã‚ˆã£ã¦ã€æ„åœ°ã§ã‚‚Enumåž‹ã®
snjx 2019/05/13
ruby

rails

æŠ€è¡“

mysql

sql
ãƒªãƒ³ã‚¯
[MySQL]ã‚¤ãƒ³ãƒ‡ãƒƒã‚¯ã‚¹ã«ã‚ˆã‚‹ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°ã€œãƒã‚°å‡ºåŠ›ã€EXPLAINã€ã‚¤ãƒ³ãƒ‡ãƒƒã‚¯ã‚¹ã€œ - Qiita
[mysqld] slow_query_log = 1 #ã‚¹ãƒãƒ¼ã‚¯ã‚¨ãƒªãƒã‚°å‡ºåŠ›æœ‰åŠ¹åŒ– slow_query_log_file = mysql-slow.log #ã‚¹ãƒãƒ¼ã‚¯ã‚¨ãƒªãƒã‚°ãƒ•ã‚¡ã‚¤ãƒ«å long_query_time = 2 #ã‚¹ãƒãƒ¼ã‚¯ã‚¨ãƒªã¨ã—ã¦èªè˜ã•ã›ã‚‹æ™‚é–“[ç§’] log-long-format #ã‚¤ãƒ³ãƒ‡ãƒƒã‚¯ã‚¹ã‚’ä½¿ç”¨ã—ã¦ã„ãªã„ã‚¯ã‚¨ãƒªã‚’ãƒã‚°ã«æ®‹ã™ ä¸Šè¨˜ã®è¨å®šã¯[mysqld]ã®ã¨ã“ã‚ã«æ›¸ã‹ãªã‘ã‚Œã°æ£ã—ãå¤‰æ•°åã‚’è§£æ±ºã—ã¦ãã‚Œãªã„ã®ã§æ³¨æ„ã€‚ slow_query_logã¯ONã§ã¯ãªã1ã¨ã—ãªã‘ã‚Œã°ã‚¹ãƒãƒ¼ã‚¯ã‚¨ãƒªãƒã‚°å‡ºåŠ›ãŒæœ‰åŠ¹åŒ–ã•ã‚Œãªã„ã®ã§æ³¨æ„ã€‚ slow_query_log_fileã®å ´æ‰€ã¯find ãƒ‘ã‚¹å -name mysql-slow.logã§æ¤œç´¢ã™ã‚Œã°ã‚ã‹ã‚‹ã€‚ã€€â†’ã€€/var/lib/mysql/mysql-slow.logã«ç”Ÿæˆã•ã‚ŒãŸã€‚ long_query_timeã§è¨å®šã—ãŸç§’æ•°ä»¥
snjx 2019/02/21
mysql

DB

è¨å®š

æŠ€è¡“

sql

ã‚·ã‚¹ãƒ†ãƒ é–‹ç™º
ãƒªãƒ³ã‚¯
1 2 3 æ¬¡ã®ãƒšãƒ¼ã‚¸