例えば、customersテーブルでcity列が'New York'の顧客のstateを'NY'に更新する場合、次のクエリを使用できます。

UPDATE customers
SET state = 'NY'
WHERE city = 'New York';

● データベース覧の確認

show databases;

● ユーザ一覧の確認

SELECT user, host FROM mysql.user;

● あるユーザー ( myuser% ) があるDBに対してどの権限を持っているか確認（全ての権限が表示されるので対象となるデータベースの権限が付与されているかどうかを調べる）

SHOW GRANTS FOR 'myuser'@'%';

https://dev.mysql.com/doc/refman/8.0/en/show-grants.html

●あるデータベースを操作する権限を持つユーザー一覧を表示させる

SELECT User FROM mysql.db WHERE db ="my-database";

● あるユーザー ( myuser% ) にDB ( mydb )への全ての権限を付与する

GRANT ALL PRIVILEGES ON `mydb`.* TO `myuser`@`%`;

● mariadb.service failed エラーの対処方法

mysql (mariaDB)起動時

systemctl start mysql

以下のようなエラーが返ってきてMySQLを起動できない時があります

Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.

・設定ファイルの参照

cat /etc/my.cnf.d/server.cnf | grep error

ログの場所が分かります　↓

log-error = /var/log/mysql/mysqld.log

・ログの参照

cat /var/log/mysql/mysqld.log

以下のように [ERROR] となっている箇所を探します

2021-10-14 12:26:52 139785906096384 [ERROR] mysqld: File '/var/lib/mysql/aria_log.00000006' not found (Errcode: 2 "No such file or directory")
2021-10-14 12:26:52 139785906096384 [ERROR] mysqld: Aria engine: log initialization failed
2021-10-14 12:26:52 139785906096384 [ERROR] Plugin 'Aria' init function returned error.
2021-10-14 12:26:52 139785906096384 [ERROR] Plugin 'Aria' registration as a STORAGE ENGINE failed.

2021-10-14 12:49:25 139975498930432 [ERROR] mysqld: Aria engine: log data error
last_log_page:   (6,0x0) is less than
checkpoint page: (7,0x2000)
2021-10-14 12:49:25 139975498930432 [ERROR] mysqld: Aria engine: log initialization failed

上記のエラーメッセージの場合は aria_log.xxxxxx でエラーが出ているので、ログファイルを全削除します。 これで再起動すれば無事起動します。

● MySQL の TEXT型の String data, right truncated: 1406 Data too long for column エラー

それは文字数オーバーのエラーです以下の文字数を超える場合に表示されるのってデータベースのカラム設計を見直しましょう。

TINYBLOB, TINYTEXT L 255 Byte
BLOB, TEXT (64 KB)
MEDIUMBLOB, MEDIUMTEXT (16 MB)
LONGBLOB, LONGTEXT (4 GB)

● 全文テキストの最小マッチ文字をセットする

最小マッチ文字を2文字にセットする

[mysqld]
innodb_ft_min_token_size=2
ft_min_word_len=2

● MariaDB 標準の 全文検索 FULLTEXT インデックスを張って高速検索する

ALTER TABLE `テーブル名` ADD FULLTEXT(`カラム名`);

● 検索してみる

通常の検索 LIKE検索（遅い）

SELECT item_name AS `numrows`
FROM `item_dt`
WHERE
`item_name` LIKE '%SSD256GB%';

全文検索（速い）

SELECT item_name AS `numrows`
FROM `item_dt`
WHERE
MATCH(`item_name`) AGAINST( "+SSD256GB" IN BOOLEAN MODE);

● インデックスを再構築する

インデックスを削除 → インデックスを再度作成 を行ないます。

・1.インデックス一覧を表示

show index from 【テーブル名】;

・2.インデックスを削除

DROP INDEX 【インデックス名】 ON 【テーブル名】;

・3.インデックスを作成

ALTER TABLE `テーブル名` ADD FULLTEXT(`カラム名`);

● MySQLのソートで null 最後にする「カラム名 is null」が遅いので高速にする

＊遅いSQL文

SELECT
    *
FROM my_table
ORDER BY
    salesrank_no is null, salesrank_no LIMIT 10;

　　↓　別々で取得して、UNION を使用します。

＊早いSQL文

(SELECT * FROM my_table ORDER BY salesrank_no is null, salesrank_no LIMIT 10)
UNION ALL
(SELECT * FROM my_table WHERE AND ( salesrank_no is null ) LIMIT 10)
ORDER BY salesrank_no is null, salesrank_no LIMIT 10;

● MySQL( MariaDB )のストレージエンジンを確認する

MySQL( MariaDB )の代表的なデータベースエンジンには次のようなものがあります

・MyISAM「MySQLが最初に持っていたデータベースエンジン。トランザクションがないかわりに高速です。ただし現在では使用しません。後継
のAriaを使用しましょう。」
・Aria「MyISAMの改良版。トランザクションが必要ないときはこちらを選択すると良いでしょう」
・InnoDB 「トランザクションが必要な場合はInnoDBを選択します」

＊すべてのテーブルのデータベースエンジンを表示する

use information_schema;
select table_schema, table_name, engine from tables;

＊データベース【my_db】のデータベースエンジンを表示

use information_schema;
select table_schema, table_name, engine from tables where table_schema = 'my_db' ;

＊テーブル作成時にデータベースエンジンを指定する

search_dt を【Aria】エンジンで作成する例

DROP TABLE IF EXISTS `search_dt`;
CREATE TABLE `search_dt` (
  `search_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `search_name` text,
  `search_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`search_id`)
) ENGINE=Aria AUTO_INCREMENT=2824 DEFAULT CHARSET=utf8;

● mysqldump で出力した SQLファイルのインポートを高速化する

pre.sql で次の内容を保存

SET @OLD_AUTOCOMMIT=@@AUTOCOMMIT, AUTOCOMMIT = 0;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS = 0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0;

post.sql で次の内容を保存

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET AUTOCOMMIT = @OLD_AUTOCOMMIT;
COMMIT;

高速なリストアコマンド

1. pre.sql
2. dump.sql (実行したいSQL文)
3. post.sql

を連続で実行します。

cat pre.sql dump.sql post.sql | mysql -A -u【ユーザー名】 -p 【DB名】

● MySQL で mysqldump の データをインポート時に MySQL server has gone away となる時の対処法

 mysql -uユーザー名 -p データベース名 < SQLファイル名

とするとエラーとなる時の対処法

1. my.cnf の場所を調べる

mysql --help | grep my.cnf

2. max_allowed_packet を増やす

vi /etc/my.cnf

[mysqld]
max_allowed_packet=32M

3. MySQLの再起動

service mysql restart

4. ダンプファイルからの復元

mysql -u USER-NAME -p DB-NAME --max_allowed_packet=16M < dump.sql

● 1. MySQLのsql_modeを確認し保存する

phpMyAdmin の 「SQL」から次のSQLを実行し、設定値を保存しておきます。 （何も表示されない場合はその旨記録しておきます）

1. sql_mode を確認する

SHOW VARIABLES LIKE "%sql_mode%";

2. グローバルスコープのsql_mode を確認する

SELECT @@global.sql_mode;

3. ローカルスコープのsql_mode を確認する

SELECT @@session.sql_mode;

● 2. MySQLのDBデータを保存する

phpMyAdmin の 「エクスポート」からDBを保存しておきます。

● mysql の ALTER TABLE で既存のテーブルに カラム 追加する

ALTER TABLE
    <テーブル名>
ADD <カラム名>  <カラム定義>
AFTER <このカラムの後に>

● カラム追加の例

my_table に 次の3つのカラム server_name text , id_name text , password_name を追加します

ALTER TABLE 
    my_table
ADD password_name VARCHAR(255) COMMENT 'サーバ' AFTER ftp_password_name ,
ADD id_name       VARCHAR(255) COMMENT 'ID' AFTER ftp_password_name ,
ADD server_name   VARCHAR(255) COMMENT 'パスワード' AFTER ftp_password_name ;

● mysql で既存のテーブル名を変更せずに　テーブル定義を変更する

ALTER TABLE
    <テーブル名>
MODIFY <カラム名>  <カラム定義>

● MySQLのテーブルの型一覧

TINYINT
SMALLINT
MEDIUMINT
INT、INTEGER
BIGINT

BOOL、BOOLEAN

DECIMAL、DEC、NUMERIC
FLOAT
DOUBLE

DATE
DATETIME
TIMESTAMP
TIME
YEAR

CHAR
VARCHAR
BINARY
VARBINARY
TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB
TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT
ENUM
SET

● MySQLの設定ファイル my.cnf の場所を調べる

mysql --help | grep my.cnf

戻り値

                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /Applications/MAMP/conf/my.cnf ~/.my.cnf 

2行目に読み込まれる順番に設定ファイルが記述されています。 上記の例だと

/etc/my.cnf
/etc/mysql/my.cnf
/Applications/MAMP/conf/my.cnf
~/.my.cnf

の順にファイルを調査しに行き読み込みます。 順に cat してファイルがなければ、次のファイル、ファイルがあればそれが mysqlの設定ファイルです。

● mysqldump で WHERE句　を使用する

テーブル名を指定して　その後ろの --where で WHERE を記述します

mysqldump -u <ユーザー名> <DB名> <テーブル名> -p --where 'user_name IS NOT NULL' > dump.sql

● MySQL で 半角スペースを含むデータを検索する

SELECT * from T1 where column1 REGEXP 0xC2A0;

引用 : http://bit.ly/3af2xzI

● Sequel Pro

https://www.sequelpro.com/
定番です。Mac用フリーのアプリ。

● tableplus

https://tableplus.com/
Windows用の一部フリーのアプリ。

● MySQL の現在のタイムゾーン設定位置を 取得する

show variables like '%time_zone%';

● MySQL の タイムゾーンを動的に変更する

SET time_zone = '+09:00';

● mysqlで同姓同名のデータのリストを出力する

SELECT * FROM テーブル1
WHERE カラム1 IN (
	SELECT
		カラム1
	FROM
		テーブル1
	GROUP BY
		カラム1
	HAVING
		COUNT(カラム1) > 1
)

● Mysql の IN句 を使用する

SELECT
	*
FROM
	my_table
WHERE
	my_column in ('斎藤', '田中', '中島');

● mysql データベース構造を diff で比較する

id : root
password : hoge

my_db_01 と my_db_02 のDB構造を比較します

diff <(mysqldump -uroot -phoge my_db_01 -d) <(mysqldump -uroot -phoge my_db_01 -d)

● 基本的な MySQL の UPDATE文の書き方

UPDATE
	item_table
SET
	delete_flg = 1
WHERE
	category_id = 43

● Sequel Pro や phpMyAdminでダウンロードした INSERT 文を UPDATE 文に変更する

INSERT INTO `dics` (`id`,  `comment`)
VALUES
	('my_text_03', 'コメントテスト);

　↓ INSERT を REPLACE に書き換えます。

REPLACE INTO `dics` (`id`,  `comment`)
VALUES
	('my_text_03', 'コメントテスト);

● MySQL の REPLACE 文とは

REPLACE は、INSERT とまったく同じように機能します。ただし、テーブル内の古い行に、PRIMARY KEY または UNIQUE インデックスに関して新しい行と同じ値が含まれている場合、その古い行は新しい行が挿入される前に削除されます。セクション13.2.5「INSERT 構文」を参照してください。

↑ この通りのロジックだとまず INSERTしにいって、同一プライマリーキーで登録が既にある場合はDELETEして再度INSERTというロジックでしょうか。

参考 : http://bit.ly/327lNeh

● MariaDB の バージョンを調べる

mysql -u root -p -e 'status'

もし Pleskをお使いの場合は

mysql -uadmin -p`sudo cat /etc/psa/.psa.shadow` -e 'status'

結果例

mysql  Ver 15.1 Distrib 10.1.36-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:          33536
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         10.1.36-MariaDB MariaDB Server
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 36 days 16 hours 59 min 3 sec

Threads: 4  Questions: 3939177  Slow queries: 8  Opens: 8496  Flush tables: 1  Open tables: 152  Queries per second avg: 1.242

10.1.36　であることがわかります。

● MySQLのビューを作成する

「view_test」というビューを新規作成します

CREATE VIEW 
	view_test 
AS
	SELECT id, name from users where is_deleted = 0;

● MySQLのビューを変更する

「view_test」というビューを再定義する感じです。 「CREATE VIEW」 →「ALTER VIEW」に変わります

ALTER VIEW 
	view_test 
AS
	SELECT id, name from users where is_deleted = 0;

● MySQLのビューを削除する

ビュー「view_test」を削除します

DROP VIEW 
	view_name 

1. まず サブクエリで作成したいのテーブルのSQLを確認します。

SELECT 
	item_id, MIN(price_no) AS MIN_PRICE_NO
FROM
	items
GROUP BY
	item_id

2. LEFT JOIN するSQL文を用意します

SELECT 
	A.* ,
	B.MIN_PRICE_NO
FROM
	item_master AS A
	LEFT JOIN TMP AS B ON A.id = B.item_id

3. サブクエリを埋め込む

ここの TMP を (サブクエリ) で置き換えるだけでOKです。

SELECT 
	A.* ,
	B.MIN_PRICE_NO
FROM
	item_master AS A
	LEFT JOIN TMP AS B ON A.id = B.item_id

　↓

SELECT 
	A.* ,
	B.MIN_PRICE_NO
FROM
	item_master AS A
	LEFT JOIN 
(
SELECT 
	item_id, MIN(price_no) AS MIN_PRICE_NO
FROM
	items
GROUP BY
	item_id
)
	 AS B ON A.id = B.item_id

● Mroongaのインストール

kusanagiの場合

kusanagi addon install mroonga

kusanagiを使ってない場合
こちらに詳しく載っています
http://asuki-yt.hatenablog.jp/entry/2018/04/03/200000

● （設定:1）MySQLの全文検索の「検索マッチ最小文字数」の設定を行う

https://dev.mysql.com/doc/refman/5.6/ja/fulltext-fine-tuning.html

単語の最小長と最大長の構成 インデックスが付けられる単語の最小長および最大長は、InnoDB 検索インデックスの場合は innodb_ft_min_token_size および innodb_ft_max_token_size、MyISAM 検索インデックスの場合は ft_min_word_len および ft_max_word_len で定義されます。これらのオプションのいずれかを変更したら、変更を有効にするために FULLTEXT インデックスを再構築してください。たとえば、2 文字の単語を検索可能にするには、オプションファイルに次の行を配置します。

[mysqld]
innodb_ft_min_token_size=2
ft_min_word_len=2

設定ファイル my.cnf を編集する

vi my.cnf

[mysqld]
# innoDBの全文検索　最小文字数
innodb_ft_min_token_size=1

# MyISAMの全文検索　最小文字数
ft_min_word_len=1

mysqlのリスタート

systemctl restart mysql

設定の確認

 show variables like '%ft%';

● （設定:2）全文検索インデックスの作成

alter table MY-TABLE add fulltext(`COLUMN-NAME`);

● （設定:2）全文検索インデックスの確認

show index in MY-TABLE;

● （設定:3）現在のDB（のテーブル）のストレージエンジンを Mroonga に変更する

ストレージエンジンを変更しなくても一見日本語全文検索ができているように見えますが、 検索文字の前後に半角文字があるデータしかマッチしません。 ストレージエンジンを Mroonga に変更する事で前後に全角文字があるデータもマッチするようになります。

現在のDB（のテーブル）のストレージエンジンを確認する

use information_schema;
select table_schema, table_name, engine from TABLES WHERE table_schema = 'MY-DB';

ストレージエンジンを Mroonga に変更する

use MY-DB
ALTER TABLE `MY-TABLE` ENGINE = Mroonga;

● 全文検索の実行

https://dev.mysql.com/doc/refman/5.6/ja/fulltext-search.html

実行モードには下記の4つありますが

IN NATURAL LANGUAGE MODE
IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
IN BOOLEAN MODE
WITH QUERY EXPANSION

これを一番よく使います。

select * from TABLE-NAME where match(COLUMN-NAME) against( '+検索文字' IN BOOLEAN MODE );

● OR 検索

select * from TABLE-NAME where match(COLUMN-NAME) against( '電車 バス' IN BOOLEAN MODE );

● AND 検索

select * from TABLE-NAME where match(COLUMN-NAME) against( '+京都 +カフェ' IN BOOLEAN MODE );

● MariaDBの日本語全文検索は実際にどれくらい早いのか？

実データで検証しました

select count(*) from MY-TABLE
+----------+
| count(*) |
+----------+
|  3431394 |
+----------+

340万データを持つテーブルを検索してみます。

・日本語全文検索

select count(*) from MY-TABLE where match(item_name) against( '+キーワード' IN BOOLEAN MODE );
+----------+
| count(*) |
+----------+
|     1451 |
+----------+
1 row in set (0.05 sec)

・LIKE検索

select count(*) from MY-TABLE where item_name like '%キーワード%';
+----------+
| count(*) |
+----------+
|     1451 |
+----------+
1 row in set (26.32 sec)

● Mroongaの最適化

http://mroonga.org/ja/docs/reference/optimizations.html

MySQLで1GBクラスの大きなデータをインポートする時、デフォルトの設定だと「MySQL server has gone away」が出てしまいます。 そこでメモリの設定を変更しましょう。

公式ドキュメント https://dev.mysql.com/doc/refman/5.6/ja/packet-too-large.html

MySQL 5.6 Server およびクライアント間で転送可能なパケットの最大サイズは 1G バイトです。
クライアントとサーバーの両方にそれぞれ max_allowed_packet 変数があるため、大きなパケットを処理する場合は、クライアントとサーバーの両方のこの変数を増やす必要があります

● max_allowed_packet を確認する

show variables like 'max_allowed_packet';

+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+
1 row in set (0.03 sec)

4MBに設定されています

● max_allowed_packet を変更する

16M に設定します

vim /etc/my.cnf.d/my.cnf

[mysqld]
max_allowed_packet = 16M

mysqlリスタート

systemctl restart mysql

● max_allowed_packet を変更する（my.cnfが変更できない場合）

mysqlへ接続して直接クエリを流しこみます

set global max_allowed_packet=16777216;

● データインポートを実行する

mysql -u USER-NAME -p DB-NAME --max_allowed_packet=16M < dump.sql

これでもエラーが出る場合はそもそも mysqldump のダンプの取り方を変えてみると良いでしょう

● mysqldumpで一行ずつのINSERT文を出力する

mysqldumpではデフォルトで全ての行を1度にINSERTするようなSQL文を吐き出します。
これをやめるオプションが　--skip-extended-insert　です。これを指定してdumpします。

mysqldump -u USER-NAME -p DB-NAME --skip-extended-insert  > dump.sql

● データの取り込み

mysql -u USER-NAME -p DB-NAME < dump.sql

MySQL5.7以降ではデフォルトで InnoDBで日本語全文検索が使えるようになりました。

● MySQLのバージョンを確認する

SELECT version();

● FULLTEXT インデックスを貼る

例1. テーブル「mytable」のカラム「title」「content」に全文検索用インデックスを作成する

ALTER TABLE mytable ADD FULLTEXT KEY (title, content) WITH PARSER ngram;

例2. インデックス名を指定して全文検索用インデックスを作成する

対象テーブル「item_table」対象カラム「item_name」
インデックス名「item_name_fulltext_idx」
でフルテキストインデックスを作成する

CREATE FULLTEXT INDEX `item_name_fulltext_idx` ON `item_table` (`item_name`) WITH PARSER ngram;

● 全文検索の設定

デフォルトは4文字以上の単語が対象ですがその対象を2文字に変更することができます。

1. my.cnf に設定を記述

/etc/my.cnf

[mysqld]
innodb_ft_min_token_size=2

2. MySQLをリスタート

3. FULLTEXTインデックスを貼り直す

一度削除してから再度作成すればOKです。

・インデックスの貼り直し

DROP INDEX item_name_fulltext_idx ON item_table ;
CREATE FULLTEXT INDEX `item_name_fulltext_idx` ON `item_table` (`item_name`) WITH PARSER ngram ;

● MySQLですべてのデータのあるカラムの先頭に文字を追加する

テーブル「category」 全データの カラム「path_name」
の先頭に「/」を追加する

UPDATE category SET `path_name` = CONCAT("/",`path_name`) WHERE category_id = 12;

● MySQLでハミング距離を取得する

1010100000
1011100001

のハミング距離を MySQLで取得してみます。

672
737

SELECT BIT_COUNT(672 ^ 737) as hd;

結果

2

● MySQLで文字数が多い順にソートする

文字数が多い順に 100件取得します

SELECT
	* , CHAR_LENGTH(カラム名) as MOJI_LENGTH
FROM
	my_table
ORDER BY
	MOJI_LENGTH DESC
LIMIT 100

「外部キー制約」はいわゆる『親テーブル - 子テーブル』というテーブル関係において 子テーブルに設定するキー制約です。

MySQLでは以下の条件を満たす時に外部キー制約を設定することができます

・親テーブルと子テーブルが両方InnoDBであること
・子テーブルのキーを設定するカラムと親テーブルの参照するカラムの型が一致していること
・親テーブルの参照するカラムにインデックスが設定されていること

● MySQLの外部キー制約の設定方法

親テーブル: user_table
子テーブル: user_detail_table
紐付けるカラム： user_table.user_id   <->  user_detail_table.user_id

ALTER TABLE
	user_detail_table
ADD FOREIGN KEY
	(user_id) 
REFERENCES
	user_table(user_id)
ON UPDATE
	親テーブル更新時の動作
ON DELETE
	親テーブル削除時の動作
;

「親テーブル更新時の動作」「親テーブル削除時の動作」に設定できる項目

（注意） CASCADE を設定したとき、小テーブルにデータを持たない親テーブルのデータを削除しようとするとエラーとなります。

mysql のTIMESTAMP型カラムのデフォルト値の設定について

古くから mysql を使ってきた方は意外と気づいてないかもしれませんが
mysql 5.6から timestam 型のカラムにはデフォルト値を明示的に指定しないといけなくなっています
（今のところ問題なく動作はしていますが。）

指定しない場合は mysql のエラーログに以下のようなエラー文が記録されているはずです

TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

このエラーをなくすには mysql の create 文で以下のように明示的に指定してあげます

create table my_table(
  id int(10) unsigned NOT NULL auto_increment,
  user_name varchar(255) NOT NULL ,
  modified_date timestamp default current_timestamp()
);

mysql のログを logrotate を使ってローテーションさせる

mysql のログ

/var/log/mysqld.log
/var/log/mysql_slow.log

などを logrotate を使ってローテーションさせます。

● logrotateのmysql設定（ /etc/logrotate.d/ ）を変更する

vi /etc/logrotate.d/mysql

/var/lib/mysql/mysqld.log {
        # create 600 mysql mysql
        notifempty
        daily
        rotate 5
        missingok
    postrotate
	# just if mysqld is really running
	if test -x /usr/bin/mysqladmin && \
	   /usr/bin/mysqladmin ping &>/dev/null
	then
	   /usr/bin/mysqladmin flush-logs
	fi
    endscript
}

/var/lib/mysql/mysqld.log を 現在運用中の mysql のログディレクトリを指定します

/var/lib/mysql/mysqld.log {

↓

/var/log/mysql_slow.log /var/log/mysqld.log {

● mysqladmin をパスワードなしで実行できるようにmy.cnf にユーザー名とパスワードを記述しておく

※ 方法1. /root/.my.cnf にユーザー名とパスワードを記述しておく

touch 600 /root/.my.cnf
chmod 600 /root/.my.cnf
vi /root/.my.cnf

[mysqladmin]
password = パスワード
user= root

※ 方法2. /etc/.my.cnf にユーザー名とパスワードを記述しておく

 vi /etc/my.cnf

[mysqladmin]
password = YOUR_PASS_WORD
user= root

● MySQLサーバのバージョンを調べる

mysql -uroot -p【ルートのパスワード】 -D mysql -e "SELECT version()"

表示例

+-----------+
| version() |
+-----------+
| 5.7.17    |
+-----------+

または MySQLにログイン中なら（接続DBはどれでもよい）

select version();

でも取得できます。

● MySQL クライアント / サーバ のバージョンを調べる

mysql -V

表示例

mysql  Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using  EditLine wrapper

クライアントバージョン 14.14
サーババージョン 5.7.17
です。

● MySQLにユーザーを追加する

1.データベース「my_db」の全ての操作可能なローカルからのみアクセス可能なユーザー「hoge」を作成する

接続元 : localhost のみ
ユーザ名 : hoge
パスワード : Fugafuga1#
データベース名 : mydb
権限 : 全て

GRANT ALL PRIVILEGES ON mydb.* TO 'hoge'@'localhost' IDENTIFIED BY 'Fugafuga1#' WITH GRANT OPTION;

● ユーザー一覧を表示

select * from user;

2. 外部から接続可能なユーザを作成する

接続元 : どこからでも可能
ユーザ名 : hoge
パスワード : fugafuga
データベース名 : mydb
権限 : SELECT, INSERT, UPDATE, DELETE のみ

GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* to hoge@"%" IDENTIFIED BY 'password' WITH GRANT OPTION;

● MySQL5.7以降で『Your password does not satisfy the current policy requirements』と表示される。

MySQL5.7以降ではデフォルトで「validate_password_policy」が「MEDIUM」に設定されています。
（アルファベット大文字、アルファベット小文字、数字、記号がかならず1文字含まれてないといけない）
これを回避するには

 SET GLOBAL validate_password_policy=LOW;

とします。
確認するには

 show variables like 'validate_password%';

とします。

MySQLで累計を求めるには変数を使うと簡単です。

SET @a;

SELECT
  data_id, price, @a:=@a+price as price_ruikei
FROM
  my_table
ORDER BY
  modified_date;

MySQL server has gone away というエラーが出ることがあります。 これは最後のMySQL接続から時間が経って、自動的にMySQLサーバがタイムアウトした時に発生します。 タイムアウト時間は PHPの場合 phpinfo(); で表示される画面の

mysql.connect_timeout	

の項目で見ることができます。デフォルト 60秒。

このタイムアウト時間を長くするには PHPの場合

ini_set('mysql.connect_timeout', 180);
ini_set('default_socket_timeout', 180);

とします。（3分に設定する場合）

またPHPなどのプログラムでMySQLクエリーを発行する前に再接続をしておくと良いです PHPフレームワークCodeIgniterの場合

$this->db->reconnect();

で再接続を行います。

MySQLで カラム「modified_date」が「現在時刻より 5分前以前に更新された」データを取得するには

SELECT
	*
FROM
	テーブル名
WHERE
	modified_date < CURRENT_TIMESTAMP + INTERVAL - 5 MINUTE ;

MINUTEのところは以下の文字が使用できます。

YEAR
MONTH
DAY
-----
WEEK
-----
HOUR
MINUTE
SECOND
-----
MICROSECOND（マイクロ秒）

MySQLでテーブル、カラムごとのコメントを作成する

create table my_dt(
	test_name varchar(200) comment 'カラムのコメント'
)
engine=MyISAM
default charset=utf8
comment='テーブルのコメント';

MySQLのカラムコメントを表示する

テーブル（my_dt）のカラムコメントを表示する

show full columns from my_dt;

MySQLのダンプコマンド【mysqldump】は一行ずつのINSERTコマンドをでダンプしません。 （複数行を一度にINSERTする） この書式ではメモリを大量に使用しますし、SQLiteのSQL文に対応しませんので一行ずつのINSERTコマンドでダンプします。

また最初に構造だけ取り出しておくのも良いです。（DBエンジンや インデックスを見直す時に便利）

● MySQLのmysqldumpで構造のみ取り出す

mysqldump --no-data -h【サーバ名】 -u【ユーザ名】 -p【パスワード】【データベース名】 > dump_table_desc.sql

● MySQLのmysqldumpで1行ずつINSERTする

mysqldump -c --order-by-primary --skip-extended-insert -h【サーバ名】 -u【ユーザ名】 -p【パスワード】【データベース名】 【テーブル名】 > dump.sql

>> -c オプションをつけるとINSERTするカラム名もファイルに書きだされます。 より小さいファイルサイズの方が良い場合はこのオプションを外すといいでしょう <<

MySQLで文字数（xx文字以上）によるSELECTをするには CHAR_LENGTH を使用する。

SELECT
	*
FROM
	table
WHERE 
	CHAR_LENGTH(`title_name`) > 20
ORDER BY
	CHAR_LENGTH(`title_name`)
limit 1000

あるテーブル「test_table」のカラム「price_no」を小数点二桁まで扱えるようにするには

ALTER TABLE 
	test_table
MODIFY
	price_no decimal(10,2) default NULL ;

とします。 (10,2)というのは 全部で10桁、小数点2桁 という意味です。 例

12345678.12

MySQL で小数点を表す型は2つあります。

・固定小数点型

decimal , numeric

DECIMAL および NUMERIC 型は真数値データ値を格納します。これらの型は、金銭データを扱う場合など、正確な精度を保持することが重要な場合に使用されます。MySQL では、NUMERIC は DECIMAL として実装されるので、DECIMAL に関する次の注意事項が NUMERIC にも同様に適用されます。

・浮動小数点型 (概数値)

float , double

FLOAT および DOUBLE 型は概数値データ値を表します。MySQL は、単精度値には 4 バイトを、倍精度値には 8 バイトを使用します。
浮動小数点値は概数値であり、真数値としては格納されないので、比較で値を真数値として扱おうとすると、問題が発生することがあります

MySQLのスローログ（実行に時間がかかったSQLクエリ）を取得するには

● 1. /etc/my.cnf に以下の文を追加

# 1秒以上かかったクエリを mysql_slow.log に保存する
slow_query_log=ON
long_query_time=1
slow_query_log_file=/var/log/mysql_slow.log

# インデックスを使わない検索を スローログに追加する
# log-queries-not-using-indexes

● 2. 次にログファイルを作成し、権限・グループをmysqlに変更

touch  /var/log/mysql_slow.log
chown  mysql  /var/log/mysql_slow.log
chgrp  mysql  /var/log/mysql_slow.log

● 3. mysqlの再起動

service mysqld stop
service mysqld start

● 4. スローログの確認方法

# mysqladmin コマンドで確認する
mysqladmin -uroot -p status
# スローログを表示する
cat /var/log/mysql_slow.log

日付（日付のみ。）を指定してデータをSELECTするには以下のように WHERE句において不等号で指定する。

例1：2018年9月8日の00:00 より前（昔）のデータを選択する

SELECT 
	test_id,
	test_name,
	test_date 
FROM
	test_dt
WHERE
	search_date < '2018-09-08';

例2：2018年9月1日〜9月4日のデータを選択する

SELECT 
	test_id,
	test_name,
	test_date 
FROM
	test_dt
WHERE
	search_date BETWEEN '2018-09-01' AND '2006-09-05' ;

例3：今日より5日以上前（昔）のデータを選択する

SELECT 
	test_id,
	test_name,
	test_date 
FROM
	test_dt
WHERE
	search_date < DATE_SUB( CURDATE(),INTERVAL 5 DAY )

例4：現在時刻から1時間以内に更新した（ modified_date が 1時間以内 ）データを選択する

SELECT * , DATE_SUB( CURDATE(),INTERVAL 1 MINUTE ) as DATE_A
FROM test_dt
WHERE modified_date > DATE_SUB( NOW(),INTERVAL 1 HOUR )

5日後の場合は DATE_ADD()を使用する

● MySQLで日時（日付と時刻）を指定してデータをSELECTする

CURDATE() の 代わりに NOW() を使用します。

（エンコードUTF-8で運用の場合。）

● 1. /etc/my.cnf に以下の設定を追加する。

[client]
default-character-set = utf8
[mysqld]
character-set-server = utf8

● 2. mysqlを再起動する。

service mysqld restart

これでも解決しない時は次を試す

● 3 . /etc/my.cnf に以下の設定を追加する。

[mysqld]
skip-character-set-client-handshake

● 4. mysqlを再起動する。

service mysqld restart

● 5.これでも改善されない場合

文字化けする
・各DB
・各テーブル
の character-set を確認すること

● コマンドラインからmysqlを実行して文字化けが発生している場合は

mysql> SET NAMES utf8;

で文字コードがutf8にセットされ文字化けがなおります。

● Windows コマンドプロンプトでの文字化け対処方法

● Windows の コマンドプロンプトを UTF-8 に設定する

chcp 65001

● 現在のエンコーディングを確認する

show variables like '%char%';

アカウント【root】 パスワード【pass】 DB名【mydb】 でパスワード入力もコマンドラインに書いてしまうには

mysql -uroot -ppass mydb [Enter]

でOK。

この例にならって mysqlのバックアップをコマンドライン1行で行うには、 アカウント【root】 パスワード【pass】 DB名【mydb】 バックアップファイル名【backup.dump】

mysqldump -uroot -ppass mydb > backup.dump [Enter]

でOK。

● ヒストリーにパスワードを残さないようにする方法。

.bash_profile に保存して再ログイン、または直接実行

export HISTCONTROL=ignorespace

これでコマンドの先頭にスペースをつけるとhistoryに記録しない設定ができました。

この状態で先頭にスペースをつけてコマンドを実行します。

 mysqldump -uroot -ppass mydb > backup.dump [Enter]

外部ホストから接続可能なユーザを作成する

・1.まず現時点で外部から接続可能なユーザが存在するかどうかを確認する

use mysql;
SELECT Host, User, Password, Select_priv, Insert_priv,Update_priv, Delete_priv FROM user;  

「Host」のところがlocalhost以外の場合外部から接続が可能です。（ % の場合すべてのIPから接続が可能です）

・2.外部から接続可能なユーザを作成する

例 ユーザ名【hoge】 パスワード【password】 データベース名【mydb】 の場合

GRANT ALL PRIVILEGES ON mydb.* to hoge@"%" IDENTIFIED BY 'password' WITH GRANT OPTION;

・3.不要になった外部接続ユーザーを削除する

delete from mysql.user where Host="%" and User="hoge";

● MySQL接続コマンド

mysql -h HOST-NAME -u USER-NAME -p

● テーブルに貼られているインデックスを表示するには

show index from 【テーブル名】;

● インデックスを作成するには

alter table 【テーブル名】 add index 【インデックス名】 (【フィールド名】);

例
テーブル「item_dt」
対象カラム「item_name」
に インデックス名「item_name_idx」 のインデックスを作成します。

ALTER TABLE item_dt ADD INDEX item_name_idx(item_name);

● TEXT型のカラムにインデックスを作成するには

TEXT型のように可変長テキストにインデックスを作成するには長さを指定します。 例（先頭255バイトまでのテキストにインデックスを貼る）

ALTER TABLE item_dt ADD INDEX item_name_idx(item_name(255));

● インデックスを削除するには

DROP INDEX 【インデックス名】 ON 【テーブル名】;

● EXPLAIN 構文を使ってクエリの実行方法を調査する

EXPLAIN 【調査したいselect文】

表示されるデータの見方

【row】：テーブル読まれた行の数 (少ない方が良い。インデックス作成で激減する)
【type】：次の順番で早い順となる
・system, const(結果が単一行)
	・eq_ref(UNIQUE or PRIMARY index使用)
	・ref (index使用の単一パス検索)
	・range ( indexの範囲検索)
	・index(index全体をスキャン)
	・ALL (全DBデータを検索)

● mysqlクエリーを少しでも早くするには

• SELECT * をやめて必要なカラムだけを取得する（これだけで全然速さが違います。）
• 適切なインデックスをはる（どう貼っていいかわからない時は、全てのカラムにインデックスを貼って EXPLAIN する手もあります。）
• 文字列後方一致検索をやめる
• テンポラリテーブルを使ったほうが早いか検討する。

CONCAT

文字列を区切り文字で連結してその結果を返します。

SELECT CONCAT( 'My', 'S', 'QL');

結果

MySQL

MySQLではサブクエリーの代わりにテンポラリテーブルを使います。 もちろんMySQL5からサブクエリーも使えるようになりましたが、テンポラリテーブルの方が速度が早いようです。

● データのSELECTとテンポラリテーブルの作成を同時に行う

CREATE TEMPORARY TABLE tmp1 ENGINE=MyISAM
	SELECT * FROM test_dt
	WHERE item_name like '%検索文字列%' ;

● あらかじめテンポラリテーブルを作成し、あとからデータを入力して、検索

CREATE TEMPORARY TABLE tmp

SELECT 'buy'tablename,id,shop FROM buy;
INSERT INTO tmp

SELECT 'eat'tablename,id,shop FROM eat;
INSERT INTO tmp

SELECT 'inn'tablename,id,shop FROM inn;
INSERT INTO tmp

SELECT * from tmp;

DROP TABLE tmp;

● mysql のヘルプを表示します。

help

● dbname という名前のデータベースを作成します。

然るべき権限（user テーブルの Create_Prev）が必要です。

create database dbname

● dbname という名前のデータベースを削除します。

然るべき権限（user テーブルの Drop_Prev）が必要です。

drop database dbname

● 存在するデータベースの一覧を得ます。

show databases

● 存在するテーブルの一覧を得ます。

データベースを選択していない場合や、他のデータベースを指定する場合は、「show tables from dbname;」という書式になります。

show tables

● システム変数表示

show variables