MySQL

<dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

<version>5.1.13</version>

</dependency>

★コマンド

CentOSの場合

yum install -y mysql mysql-server

/etc/init.d/mysqld start

Ubuntuの場合

sudo apt-get install mysql-server

mysql --version

mysql -p

mysql -u root -p

mysql -u root -pxxx

mysql -h xxx.xxx.xxx.xxx -u root -pxxx

exit / quit

★データ型

数値 int, double

文字列 char, varchar, text

日付 date, datetime

その他 enum

id int not null auto_increment primary key,

name varchar(255),

email varchar(255) unique,

password char(32),

score double,

sex enum('male', 'female') default 'male',

memo text,

created datetime,

key score (score)

★サンプル

mydb.sql

drop database if exists mydb;

create database mydb;

use mydb;

drop table if exists test;

CREATE TABLE test (

id INT(3) AUTO_INCREMENT NOT NULL PRIMARY KEY,

name CHAR(10) NOT NULL,

address VARCHAR(50) DEFAULT 'Tokyo',

birthday DATE

);

INSERT INTO test (name,address,birthday) VALUES

('Andy','Tokyo','1980-01-01'),

('Andy','Tokyo','1980-01-01'),

('Andy','Tokyo','1980-01-01');

★データの出力・入力

出力

SELECT * FROM テーブル名 INTO OUTFILE '/tmp/data.txt'

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"';

入力

LOAD DATA LOCAL INFILE '/tmp/data.txt' INTO TABLE テーブル名

FIELDS TERMINATED BY ',' IGNORE 1 LINES;

LOAD DATA INFILE '/tmp/data.txt' INTO TABLE テーブル名

FIELDS TERMINATED BY ',' ENCLOSED BY '"'

LINES TERMINATED BY '\n';

※デフォルトはtabで区切り、nullは\n

ファイル内容

1,Tom,Osaka,1981-10-09

2,Mike,Fukuoka,1982-02-23

主キーはauto_incrementする場合

,Tom,Osaka,1981-10-09

,Mike,Fukuoka,1982-02-23

★エンジンについて

show engines;

show table status;

show innodb status;

エンジンタイプ

・MyISAM

特徴：全文検索（FullTextーIndex）

得意：COUNT() 、INSERT

・InnoDB

特徴：より多いメモリ・ディスクが必要、トランザクションと外部キーがOK、ビッグデータに向け

得意：主キー検索、UPDATE

★便利なSQL

・年齢の計算

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(now()) - TO_DAYS(@birthday)), '%Y') + 0;

・時間差の計算

SELECT UNIX_TIMESTAMP(@datetime1) - UNIX_TIMESTAMP(@datetime2);

単位：秒

引数：datatime型で‘yyyy-mm-dd hh:mm:ss’

・重複回数=Nのデータを検索

SELECT xxx FROM table GROUP BY xxx HAVING COUNT(*) = N;

・データベース容量の確認

SELECT

table_schema AS 'DB Name',

Round( Sum( data_length + index_length ) / 1024 / 1024, 3 ) AS 'DB Size (MB)',

Round( Sum( data_free ) / 1024 / 1024, 3 ) AS 'Free Space (MB)'

FROM information_schema.tables

GROUP BY table_schema ;

★重要なログ

※/etc/my.cnfに設定

Error Log

log_error=/var/log/mysql/mysqld.log

General Query Log

general_log=ON

general_log_file=/var/log/mysql/general.log

Slow Query Log (> long_query_time)

slow_query_log=ON

slow_query_log_file=/var/log/mysql/mysql-slowquery.log

★エラーの解決方法

Error Code: 1093

You can't specify target table 'xxx' for update in FROM clause

INSERT INTO xxx ( type, code ) VALUES (

'A', (

SELECT IFNULL( MAX( code ) + 1, 10001 )

FROM xxx WHERE type='A' )

);

解決方法：サブクエリの場合、tempテーブルを利用

INSERT INTO xxx ( type, code ) VALUES (

'A', (

SELECT IFNULL( max_code + 1, 10001 )

FROM ( SELECT MAX( code ) max_code FROM xxx WHERE type='A' ) temp )

);

Error Code: 1148

The used command is not allowed with this MySQL version

解決方法：

１：/etc/mysql/my.cnfを編集

[mysql]

local-infile = 1

[client]

loose-local-infile = 1

２：再起動

sudo service mysql restart

Error Code: 1175

You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

解決方法：

SET SQL_SAFE_UPDATES = 0;

UPDATE xxx SET ....

Warning: mysqldump: unknown variable 'loose-local-infile=1'

解決方法：

mysqldump --no-defaults -u root -p1234 your_db your_table > /path/to/dump.sql

★SQLの実行時間のみを調べる

cat test.sql | mysql -vvv -u<xxx> -p<xxx> -h <endpoint> <DB> | tail -n 3 | head -n 1

echo "SELECT..." | mysql -vvv -u<xxx> -p<xxx> -h <endpoint> <DB> | tail -n 3 | head -n 1

mysql -vvv -u<xxx> -p<xxx> -h <endpoint> <DB> < ./test.sql | tail -n 3 | head -n 1

★データクリア

mysql -Nse 'show tables' -u<xxx> -p<xxx> -h <endpoint> <DB> | while read table; do mysql -e "truncate table $table" -u<xxx> -p<xxx> -h <endpoint> <DB>; done

ダンプファイル内の件数確認

cat data.dump | grep "INSERT INTO" | sed -e 's/),(/\n/g' | wc -l

ls *.dump | while read file; do cat $file | grep "INSERT INTO" | sed -e 's/),(/\n/g' | wc -l; done

★基本操作

show databases;

use DB名;

show tables; または show tables from database_name;

selet * from テーブル名;

show index from テーブル名; = show keys from テーブル名;

create database DB名;

drop database DB名;

create table テーブル名(...)

describe テーブル名; = desc テーブル名;

drop table テーブル名;

delete table テーブル名;

TRUNCATE TABLE テーブル名;

show status;

show status like "Connections";

show status like "Uptime";

show status like "Slow_queries";

show status like 'Handler_read%'; インデックス状況

SELECT SYSDATE(), SLEEP(2), SYSDATE();

select * from test \G 縦表示

select * from test limit 3, 2;

select * from test order by rand() limit 1;

SELECT * FROM table LIMIT 取得件数 OFFSET 開始位置

SELECT * FROM table LIMIT 開始位置, 取得件数

show columns from table_name from database_name;

または show columns from database_name.table_name;

show grants for user_name;

show privileges;

show logs;

show warnings; -- 最後に実行されたSQLが対象

show errors; -- 最後に実行されたSQLが対象

-- ユーザーを設定

grant select on DB名.* to 'ユーザ名'@'ホスト名' identified by 'パスワード';

-- すべてホスト

grant select,insert,update,delete on *.* to 'andy'@'%' identified by 'xxx';

grant all on mydb.* to 'andy'@'localhost' identified by 'xxx';

-- パスワードなし

grant select,insert,update,delete on mydb.* to 'andy'@'localhost' identified by '';

-- AWS RDS

grant all on `%`.* to 'ユーザ名'@'%' identified by 'パスワード';

-- MySQL

grant all on *.* to 'ユーザ名'@'%' identified by 'パスワード';

SELECT @@autocommit;

SET AUTOCOMMIT=0;

★DB作成サンプル

CREATE DATABASE xxx DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE USER 'xxx'@'localhost' IDENTIFIED BY 'xxx';

CREATE USER 'xxx'@'%' IDENTIFIED BY 'xxx';

GRANT ALTER, CREATE, DELETE, DROP, INDEX, INSERT, SELECT, UPDATE ON xxx.* TO 'xxx'@'localhost';

GRANT ALTER, CREATE, DELETE, DROP, INDEX, INSERT, SELECT, UPDATE ON xxx.* TO 'xxx'@'%';

★テーブル変更操作

テーブル名の変更

ALTER TABLE 旧テーブル名 RENAME 新テーブル名;

カラムの名前

ALTER TABLE テーブル名 CHANGE 旧カラム名 新カラム名 varchar(200);

alter table test add xxx varchar(255) after name;

alter table test drop xxx;

alter table test add index xxx (name);

alter table test drop index xxx;

★文字コード

CREATE DATABASE DB名 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER DATABASE DB名 DEFAULT CHARACTER SET 'utf8';

CREATE TABLE テーブル名(...)ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE テーブル名 CHARACTER SET 'utf8';

show variables like 'char%';

set character_set_database =utf8;

★実行されたSQL文のログ

set global general_log=1;

show variables like 'general_log%';

★Query cacheのクリア

show status like 'Qcache%';

reset query cache;

| Qcache_free_blocks

| Qcache_free_memory

| Qcache_hits

| Qcache_inserts

| Qcache_lowmem_prunes メモリ不足の時

| Qcache_not_cached Cache不可、QUERY_CACHE_TYPEにより

| Qcache_queries_in_cache ※0になること

| Qcache_total_blocks

★lockの確認とプロセスの削除(例：実行中のSQL)

SHOW PROCESSLIST;

SHOW FULL PROCESSLIST;

SHOW FULL PROCESSLIST \G; 垂直表示

KILL <プロセスId>;

★NOT FOUNDに関する注意点

MySQL 5.5以前

・カーソルがデータセットの最後に達した時

MySQL 5.6以降

・カーソルがデータセットの最後に達した時

・SELECT...INTOで行が取得されない時

DELIMITER //

DROP PROCEDURE IF EXISTS myProcedure//

CREATE PROCEDURE `myProcedure` (IN fromId INT, IN toId INT)

BEGIN

DECLARE organizationId INT;

DECLARE userName VARCHAR(255);

DECLARE exitFlag INT DEFAULT FALSE;

DECLARE organizationCursor CURSOR FOR

SELECT id FROM organization WHERE id >= fromId AND id <= toId;

DECLARE EXIT HANDLER FOR NOT FOUND SET exitFlag = TRUE;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

BEGIN

SELECT 'EXCEPTION!!!';

ROLLBACK;

END;

DECLARE CONTINUE HANDLER FOR SQLWARNING

BEGIN

SELECT 'WARNING!!!';

ROLLBACK;

END;

SELECT concat('INPUT: fromId is ', fromId);

SELECT concat('INPUT: toId is ', toId);

IF fromId > toId THEN

SELECT 'fromId MUST be less than toId!!!';

SET exitFlag = TRUE;

END IF;

OPEN organizationCursor;

WHILE exitFlag DO

FETCH organizationCursor INTO organizationId;

SELECT u.name INTO userName

FROM user u

JOIN organization o ON u.id = o.user_id

WHERE o.id = organizationId;

END WHILE;

CLOSE organizationCursor;

END//

DELIMITER ;