個人的によく使うMySQL操作まとめ

はじめに

自分用メモなので書いてある内容がバラバラですがご了承ください

基本操作

select

SELECT * FROM table1 WHERE column1 = 'xxx' AND column2 > 0;

大文字小文字を区別したい時

SELECT * FROM table1 WHERE BINARY column1 LIKE 'pattern';

内部結合

SELECT * FROM table1 
    INNER JOIN table2 ON table1.column1 = table2.column2 
    WHERE table1.column1 = 'xxx';

insert

INSERT INTO table1(column1, column2) values('yyy', 1);

update

UPDATE table1 SET column1 = 'zzz' WHERE column2 > 2;

index 操作

index を追加

ALTER TABLE table1 INDEX index1(column1);

複合index を追加

ALTER TABLE table1 ADD INDEX index1(column1, column2);

index を削除

ALTER TABLE table1 DROP INDEX index1;

index を確認

SHOW INDEX FROM table1;

最大キー長

INDEXのキーは
MySQL5.6以前 → デフォルト設定で767bytesまで
MySQL5.7以降 → デフォルト設定で3072bytesまで

utf8の場合
varchar(255) * 3bytes = 765bytes < 767bytes
varchar(1024) * 3bytes = 3072bytes

utf8mb4の場合
varchar(191) * 4bytes = 764bytes < 767bytes
varchar(768) * 4bytes = 3072bytes

参考

テーブル操作

テーブルの中身を空にする

TRUNCATE TABLE table1;

　　
この時，外部キーを使用していて

Cannot truncate a table referenced in a foreign key constraint ...

のようなエラーが出た場合，一時的に外部キーの制約を外して対応

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE table1;
SET FOREIGN_KEY_CHECKS = 1;

テーブルを削除する

DROP TABLE table1, table2;

カラムを追加する

ALTER TABLE table1 ADD column1 column_definition;

warning の中身を見たい時

SHOW WARNINGS;

mysqldump関連

export

mysqldump -u user_name -p db_name > /path/file.sql

テーブル指定

mysqldump -u user_name -p db_name table_name1 table_name2 > /path/file.sql

where句の指定

mysqldump -u user_name -p db_name table_name --where="column1 = 'xxx'" > /path/file.sql

import

mysql -u user_name -p db_name < /path/file.sql

この時、import先のDBは先に作成しておく必要がある。

my.cnf設定

my.cnf場所検索

mysql --help | grep my.cnf

my.cnf設定

my.cnf編集

sudo vi /etc/my.cnf

以下を追記

[mysqld]
character-set-server=utf8mb4
skip-character-set-client-handshake

[client]
default-character-set=utf8mb4

mysqlを再起動

sudo /etc/init.d/mysqld restart