MySQLコマンドやSQL文に関する各種メモ書き

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

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

systemctl restart 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 /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 でエラーが出ているので、ログファイルを全削除します。 これで再起動すれば無事起動します。

No.2074
10/14 12:58

edit

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

● 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)
No.1963
03/08 21:49

edit

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

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

最小マッチ文字を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(`カラム名`);
No.1893
11/06 11:15

edit

全文検索

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

● 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;
No.1892
10/26 19:14

edit

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

● 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;
No.1891
10/26 18:23

edit

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

● 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名】
No.1890
10/26 15:58

edit

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

● 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
No.1888
10/23 17:57

edit

phpmyadminによるDB移行

● 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を保存しておきます。

No.1726
03/30 14:54

edit

mysql カラム 追加する ALTER TABLE

● 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
No.1697
09/21 08:17

edit

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

● 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の設定ファイルです。

No.1693
07/05 17:49

edit

mysqldump で WHERE句 を使用する

● mysqldump で WHERE句 を使用する

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

mysqldump -u <ユーザー名> <DB名> <テーブル名> -p --where 'user_name IS NOT NULL' > dump.sql
No.1671
01/22 10:34

edit

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

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

SELECT * from T1 where column1 REGEXP 0xC2A0;

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

No.1660
01/17 16:29

edit

Mac , Windows の MySQL クライアント

● Sequel Pro

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

● tableplus

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

No.1638
12/06 16:30

edit

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

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

show variables like '%time_zone%';

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

SET time_zone = '+09:00';
No.1633
12/02 10:37

edit

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

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

SELECT * FROM テーブル1
WHERE カラム1 IN (
	SELECT
		カラム1
	FROM
		テーブル1
	GROUP BY
		カラム1
	HAVING
		COUNT(カラム1) > 1
)
No.1625
11/18 12:21

edit

Mysql の IN句 を使用する

● Mysql の IN句 を使用する

SELECT
	*
FROM
	my_table
WHERE
	my_column in ('斎藤', '田中', '中島');
No.1597
10/04 12:42

edit

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

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

id : root
password : hoge

my_db_01my_db_02 のDB構造を比較します

diff <(mysqldump -uroot -phoge my_db_01 -d) <(mysqldump -uroot -phoge my_db_01 -d)
No.1591
09/26 15:17

edit

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

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

UPDATE
	item_table
SET
	delete_flg = 1
WHERE
	category_id = 43
No.1587
09/10 11:29

edit

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

● 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

No.1541
07/04 10:32

edit

MariaDB の バージョンを調べる

● 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 であることがわかります。

No.1521
05/29 09:48

edit

MySQLでビューの作成と変更と削除

● 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 
No.1334
10/30 17:04

edit

MySQLでサブクエリの結果とLEFT JOIN する

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
No.1331
10/29 11:20

edit

MySQL(Maria DB)で全文検索 (FULLTEXT INDEX) を使用する

● 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

No.1299
10/26 20:06

edit

全文検索

MySQLデータインポート時の「MySQL server has gone away」エラー対処法

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
No.1298
10/04 10:54

edit

MySQL標準の日本語全文検索を使用する

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 ;
No.1274
10/26 20:06

edit

全文検索

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

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

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

UPDATE category SET `path_name` = CONCAT("/",`path_name`) WHERE category_id = 12;
No.1273
07/11 12:05

edit

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

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

1010100000
1011100001

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

672
737
SELECT BIT_COUNT(672 ^ 737) as hd;

結果

2
No.1271
07/10 19:09

edit

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

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

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

SELECT
	* , CHAR_LENGTH(カラム名) as MOJI_LENGTH
FROM
	my_table
ORDER BY
	MOJI_LENGTH DESC
LIMIT 100
No.1256
06/23 14:46

edit

MySQLの外部キー制約

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

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
	親テーブル削除時の動作
;

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

設定文字列 動作
RESTRICT エラーを発生させる(デフォルト値)
NO ACTION エラーを発生させる(RESTRICTと同じ挙動)
CASCADE 親テーブルと同じ処理(値の更新、データの削除)を行う
SET NULL 子テーブルにNULLをセットする

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

No.1161
08/25 17:06

edit

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

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()
);
No.1115
04/03 22:34

edit

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

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
No.1114
04/24 13:42

edit

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

● 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
です。

No.1105
03/19 22:52

edit

MySQLにユーザーを追加する

● 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%';

とします。

No.1085
03/01 21:45

edit

MySQLで累計を求める

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

SET @a;
SELECT
  data_id, price, @a:=@a+price as price_ruikei
FROM
  my_table
ORDER BY
  modified_date;
No.1023
04/10 15:15

edit

MySQL server has gone away のエラー対処

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();

で再接続を行います。

No.1015
03/30 14:05

edit

エラー対処

MySQLでx分、x日前のデータを取得する( INTERVAL )

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

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

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

YEAR
MONTH
DAY
-----
WEEK
-----
HOUR
MINUTE
SECOND
-----
MICROSECOND(マイクロ秒)
No.981
01/01 15:11

edit

日付

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

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;
No.975
09/10 17:48

edit

MySQLのダンプコマンド【mysqldump】で一行ずつのINSERT文でダンプファイルを作成する

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

No.898
10/26 18:18

edit

IN演算子の使い方

あるリストの中に存在するデータを取得するときなどにIN演算子を使用します。

使い方は

リストの中に存在するデータを取得する
WHERE col_name IN (value, ...)
リストの中に存在しないデータを取得する
WHERE col_name NOT IN (value, ...)

具体的には

SELECT * FROM my_table WHERE data_id IN(1,3,5,7,9,11);

とします。

No.861
10/21 14:23

edit

MySQLテーブルのコピーを作成する

hogeテーブルをfugafugaというテーブルにコピーする方法。

CREATE TABLE fugafuga LIKE hoge;
INSERT INTO fugafuga SELECT * FROM hoge;

No.821
10/13 13:47

edit

最新x件を残して残りすべてを削除するSQL

最新x件を残して残りすべてを削除するSQLはMySQLのユーザー変数を使って下記のように記述します。

(例ではキーとなるカラムを持たない検索ログの場合。)

SET @d = ( SELECT search_date FROM my_table ORDER BY search_date DESC limit 100,1 );
DELETE FROM my_table WHERE search_date < @d;
No.769
01/26 17:10

edit

検索したリストの件数カウントを高速化する

よく 検索結果などに『◯◯件 ヒットしました』と表示するWEBアプリがあります。

その時に,

件数を取得するSQL文は

SELECT * FROM data_table;
SELECT count(*) AS count FROM data_table;

とすることが多いと思いますが、SELECT時に下記のように「SQL_CALC_FOUND_ROWS」を追加しておいて、その後 FOUND_ROWS() を記述するとかなり高速に取得できます。

SELECT SQL_CALC_FOUND_ROWS * FROM data_table;
SELECT FOUND_ROWS() AS count

がSQL文が複雑な場合は逆に遅くなることもあるようです。

http://ma-bank.com/item/998

No.768
10/07 17:57

edit

高速化

テーブルの破損を修復する

テーブル( my_table )の破損をチェックするには

check table my_table

テーブル( my_table )の破損を修復するには

repair table my_table

No.766
01/17 00:32

edit

MySQLのORDER BY で NULL値を最後にしてソートする

ORDER BY で NULL値を最後にしてソートする方法

オラクルの場合だと簡単に

■ ORACLE

ORDER BY my_field ASC NULL LAST

と記述すればOKですが、MySQLでは次のように記述します。

■ MySQL

ORDER BY IF(ISNULL(my_field),1,0),my_field;

または少し簡単に以下のように記述します

ORDER BY my_field IS NULL, my_field ASC;


No.762
04/19 10:47

edit

TEXT / BLOG型にインデックスをはる

MySQLでTEXT / BLOG型にインデックスをはろうとするとエラーとなります。

そこで、サイズを指定してインデックスをはります。

対象テーブル:data_dt
インデックス名:text_name_idx
カラム:text_name

の時下記のように記述します。

create index text_name_idx on data_dt(text_name(255));
No.757
11/24 15:35

edit

MySQL で文字数(xx文字以上)によるSELECT

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

SELECT
	*
FROM
	table
WHERE 
	CHAR_LENGTH(`title_name`) > 20
ORDER BY
	CHAR_LENGTH(`title_name`)
limit 1000
No.756
06/23 14:39

edit

別サーバのmysqlに接続し、SQL文ファイルを実行する

別サーバのmysqlに接続する

mysql -h ホスト名 -uユーザー名 -p DB名

別サーバのmysqlに接続し、SQL文ファイルを実行する

mysql -h ホスト名 -uユーザー名 -p DB名 < テキストファイル名

とすると、パスワード入力が促され、入力後に実行されます。


No.737
06/22 18:50

edit

MySQLで小数点をもつ数値型 decimal を使う

あるテーブル「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 バイトを使用します。
浮動小数点値は概数値であり、真数値としては格納されないので、比較で値を真数値として扱おうとすると、問題が発生することがあります
No.701
08/19 10:56

edit

mysqlのスローログの取得し /var/log/mysql_slow.log に保存する

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
No.643
03/28 17:12

edit

高速化

MySQL メモリチューニング

現在のMySQLの設定をみるには MySQLから

show variables;

MySQLの設定は( /etc/my.cnf )を書き換える。

設定例は

my-huge.cnf(1G〜2Gバイトのメモリを持つMySQL専用サーバー向け)

key_buffer = 384M
sort_buffer_size = 2M
read_buffer_size = 2M

my-large.cnf(512Mバイト程度のメモリを持ち,MySQL専用となる機械向け)

key_buffer = 256M
sort_buffer_size = 1M
read_buffer_size = 1M

my-medium.cnf(32M〜64Mバイトのメモリを持つMySQL専用サーバーか,128Mバイトのメモリを持つサーバー向け)

key_buffer = 16M
sort_buffer_size = 512K
read_buffer_size = 0

my-small.cnf(64Mバイト以下のメモリの小規模サーバー向け)

key_buffer = 16K
sort_buffer_size = 64K
read_buffer_size = 0

このあたりを参考に必ず設定する。

またMySQL ABでは,「key_buffer」の値はマシンに搭載しているメモリーの1/4を推奨値としている。

http://vine-linux.ddo.jp/linux/sql/mycnf.php

http://www.819410.com/FreeBSD6/shop/-145.html


No.638
10/06 15:13

edit

高速化

MySQLデータのサルベージ(データファイルからの復元)

MySQLのデータを<b>直接データファイル群から復元</b>するには以下のようにする。

1. データのバックアップ

MySQLのデータは

/var/lib/mysql/DB名/

にあるので(RedHat系Linuxの場合)直接(/DB名/)以下の全データを保存してバックアップする。

2. データの整合性のチェック

myisamchk *.MYI
myisamchk *.MYI | grep worning

3. データのリストア

リストアしたいマシンの

/var/lib/mysql/

以下に直接データディレクトリをコピーする。/var/lib/mysql/DB名)

また権限は

所有者:mysql
グループ:mysql
ディレクトリ「DB名」の権限:700(drwx------)
「DB名」内のファイルの権限:660(-rw-rw----)

とすればよい。

4. mysqlの権限が厳密で気にしなければならない場合はこの状態で mysqldump をして、いったん保存。DBを削除して再度ダンプファイルから構築する。



No.637
04/19 11:31

edit

MySQL のテーブルに ユニーク制約 を追加する

テーブルに ユニーク制約を追加するには

ALTER TABLE table_name ADD CONSTRAINT UNIQUE(column_name);

と記述します。

No.628
04/15 10:18

edit

MySQLで日付データをSELECT時に年・月・日に分解する【DATE_FORMAT】

SELECT時に日付カラム( 2007-01-23 12:30:59 )を年・月・日に分解して取得すると便利です。

SELECT
	DATE_FORMAT(CurDate(),'%Y') as year ,
	DATE_FORMAT(CurDate(),'%m') as month ,
	DATE_FORMAT(CurDate(),'%d') as day
DATE_FORMAT のオプション(指定子)は以下の通り

指定子	説明
%M	月の名前(January..December)。
%W	曜日名(Sunday..Saturday)。
%D	英語のサフィックス付きの日付(0th、1st、2nd、3rd など)。
%Y	4 桁の数値で表した年。
%y	2 桁の数値で表した年。
%X	日曜日を週の最初の日とした場合の週に使用する、4 桁の数値で表した年。%V と組み合わせて使用。
%x	月曜日を週の最初の日とした場合の週に使用する、4 桁の数値で表した年。%v と組み合わせて使用。
%a	略式の曜日名(Sun..Sat)。
%d	数値で表した日付(00..31)。
%e	数値で表した日付(0..31)。
%m	数値で表した月(00..12)。
%c	数値で表した月(0..12)。
%b	略式の月名(Jan..Dec)。
%j	年間を通した日にち(001..366)。
%H	時(00..23)。
%k	時(0..23)。
%h	時(01..12)。
%I	時(01..12)。
%l	時(1..12)。
%i	数値で表した分(00..59)。
%r	12 時間形式の時刻(hh:mm:ss に続けて AM または PM)。
%T	24 時間形式の時刻(hh:mm:ss)。
%S	秒(00..59)。
%s	秒(00..59)。
%f	マイクロ秒(000000..999999)。
%p	AM または PM
%w	曜日(0=Sunday..6=Saturday)。
%U	日曜日を週の最初の日とした場合の週(00..53)。
%u	月曜日を週の最初の日とした場合の週(00..53)。
%V	日曜日を週の最初の日とした場合の週(01..53)。%X と組み合わせて使用。
%v	月曜日を週の最初の日とした場合の週(01..53)。%x と組み合わせて使用。
%%	リテラルの ‘%’。
その他の文字はいずれも、解釈されずにそのまま結果にコピーされる。

参考:http://blog.tofu-kun.org/070620123532.php

No.552
03/26 14:32

edit

日付

MySQLでランダムにデータをとりだす。を高速化する。

MySQLでランダムにデータを「10件」取り出すには

SELECT * FROM table ORDER BY rand() limit 0 10;

とします。

ちなみにpostgreSQLでは

SELECT * FROM table ORDER BY random();

と書きます。

しかしこれはテーブル全件を走査するので非常に遅い。

そこで少し早くするには以下のようにします。

SELECT id , data FROM table ORDER BY rand() ;

で取り出したいカラムのみを指定する。

これだけでずいぶんと速くなります。

No.467
04/16 16:51

edit

ランダム

MySQLで重複を取り除いてカウントする

普通にカウントするには

SELECT count(*) FROM table1

としますが、このとき重複する行を取り除いてカウントするには

SELECT count( DISTINCT id ) FROM table1

とします。

ちなみに

SELECT count(*) FROM table1

より

SELECT count(id) FROM table1

の方が高速だと言われています。

No.466
03/26 14:33

edit

MySQLクエリ結果のキャッシュ

MySQLにはクエリキャッシュの機能があり、これをONにするとクエリ結果をキャッシュから読み出すことが出来て高速な動作が期待できる。

1.まず現在クエリキャッシュが有効かどうかを調べる

show variables like 'query_cache_%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 1048576 | 
| query_cache_min_res_unit     | 4096    | 
| query_cache_size             | 0       | 
| query_cache_type             | ON      | 
| query_cache_wlock_invalidate | OFF     | 
+------------------------------+---------+
5 rows in set (0.00 sec)

【query_cache_size】が【0】なのでクエリキャッシュが有効ではないことがわかる

2.クエリキャッシュを有効にするには

「query_cache_type」をONに、「query_cache_size」を0より大きな適切な値に設定する必要がある。

/etc/my.cnf の [mysqld] の項目にクエリキャッシュに関する記述を追加する

[mysqld]
# (query_cache_limit)これより大きい結果はキャッシュしない
# (query_cache_min_res_unit) 4K が推奨値
# (query_cache_size)クエリキャッシュに割り当てるメモリ(Bytes)
# (query_cache_type) 0:OFF 1:ON 2:DEMAND
query_cache_limit=1M
query_cache_min_res_unit=4k
query_cache_size=24M
query_cache_type=1

3.mysqlをリスタート

/etc/init.d/mysqld restart
(または)service mysqld restart

4. キャッシュ状態を確認する。

<pre>SHOW STATUS LIKE 'Qcache%';</pre>

でクエリキャッシュの状態を確認する。

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 2        | 空きブロック数
| Qcache_free_memory      | 24213616 | 空きメモリのサイズ (バイト)
| Qcache_hits             | 868      | クエリのヒット数 
| Qcache_inserts          | 900      | キャッシュに入れられたクエリの数
| Qcache_lowmem_prunes    | 0        | メモリが足りないために削除された数
| Qcache_not_cached       | 21       | キャッシュに入れられなかったクエリの数
| Qcache_queries_in_cache | 846      | キャッシュ内のクエリの数 
| Qcache_total_blocks     | 1711     | ブロックの領域の合計
+-------------------------+----------+
8 rows in set (0.00 sec)

【Qcache_lowmem_prunes】を注意深く見ればいいことがわかります。

5. キャッシュ

キャッシュは SELECT文にのみ適用される。

MySQL4.1以前を使用している場合 SQL文 の 'SELECT' の前に スペースが入っているとクエリキャッシュが有効にならないので注意。

(MySQL は先頭3文字のみを見て SEL であった場合のみクエリキャッシュに格納します。)


No.465
03/07 19:14

edit

高速化

MySQLでデータを全件削除後、auto_incrementの値をリセットする

MySQLで「table1」というテーブルのデータを全件数削除するには

DELETE from table1

としますが、このとき auto_increment を指定してあるカラムがあると、そのauto_incrementの値はそのまま残ります。

(次にデータをINSERTした時に1番から始まらない)

なので auto_increment 値をリセットします

ALTER TABLE table1 PACK_KEYS =0 CHECKSUM =0 DELAY_KEY_WRITE =0 AUTO_INCREMENT =1

これでOK。

PACK_KEYS

PACK_KEYS は MyISAM テーブルとだけ効果を発揮します。小さいインデックスを持ちたければ、このオプションを1に設定してください。これは通常更新スピードを遅くし、読み込みを早くします。オプションを0に設定すると、全てのキー パッキングが無効になります。これを DEFAULT に設定すると、ストレージ エンジンには長い CHAR や VARCHAR カラムだけをパックするように指令が出ます。
もし PACK_KEYS を利用しなければ、デフォルトでは文字列をパックしますが、数字はパックしません。もし PACK_KEYS=1 を利用すると、数字もパックされます

CHECKSUM

MySQL に全ての行のライブ チェックサムを維持させたければこれを1に設定してください。(これはテーブルが変更される度に MySQL が自動的に更新するチェックサムです。)これはテーブルの更新スピードを少し遅くしますが、壊れたテーブルを見つけるのが早くなります。CHECKSUM TABLE ステートメントはチェックサムをリポートします。(MyISAM のみです。)

DELAY_KEY_WRITE

DELAY_KEY_WRITE
キー更新をテーブルが閉じられる時まで遅らせたければこれを1に設定してください。項4.2.3. 「システム変数」 内の delay_key_write システム変数についての説明を参照してください。(MyISAM のみです。)
No.458
08/12 21:43

edit

MySQLで日付を指定してデータをSELECTする

日付(日付のみ。)を指定してデータを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() を使用します。

No.317
12/26 20:21

edit

日付

正規表現を使って検索する

正規表現を使って行を検索するには

WHERE REGEXP '正規表現'

とする。

SELECT
  id ,
  artist_name
FROM
  artist_dt
WHERE
  artist_name REGEXP '^[a-z]+$';
また、エスケープシーケンス(\)を使う場合、シーケンスが1つの場合はMySQLに対するエスケープとみなされますので、正規表現としてのエスケープとする場合は2つ並べることになります。
バックスラッシュにマッチ
SELECT '\\' REGEXP '\\\\';

ドット(正規表現ではなく文字としてのドット)にマッチ
SELECT '.' REGEXP '\\.';

シングルクォートにマッチ
SELECT '\'' REGEXP '\'';

■ 注意

MySQLでは日本語を含む正規表現は使用できません。

無理やり使う場合は下記のように記述します

× artist_name REGEXP '^[ア-オ]+'
◯ artist_name REGEXP '^(ア|イ|ウ|エ|オ)+'


日本語を含む正規表現を使用したい場合は下記の mregexp を使用します。

http://www.irori.org/tool/mregexp.html


No.294
04/19 10:29

edit

正規表現

mysqlへ外部から接続できないようにする。(外部接続ポート3306を閉じる)

1. /etc/my.cnf の

[mysqld] セクション内に

skip-networking

を追記する

2. mysqlをリスタート(再起動)する

/etc/init.d/mysqld restart
No.272
02/10 09:55

edit


mysqlで日本語が文字化けするときは?

(エンコード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%';
No.271
03/07 18:43

edit

日本語

mysql や mysqldump への接続時のパスワード入力を省略する

アカウント【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]
No.251
02/12 11:17

edit

MySQLサーバステータス

サーバステータスは、様々なサーバの状態を返します。

現在のサーバステータスを確認するには、以下のSQL文を発行します。

SHOW STATUS;

SHOW STATUS LIKE 'Qcache%';

http://www.limy.org/program/db/mysql/mysql_status.html

No.220
02/10 10:00

edit


MySQLに外部ホストから接続する

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

まず状態を確認する

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

外部から接続可能なユーザを作成する 例 ユーザ名【hoge】 パスワード【password】 データベース名【mydb】 の場合

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

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

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

● MySQL接続コマンド

mysql -h HOST-NAME -u USER-NAME -p
No.180
10/04 09:55

edit


Mysql rootユーザのパスワード設定

rootパスワードを【foo】に設定する

mysqladmin -u root password foo
mysqladmin reload
No.177
02/10 09:59

edit


MySQLの検索を高速にするインデックスの作成

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

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 する手もあります。)
  • 文字列後方一致検索をやめる
  • テンポラリテーブルを使ったほうが早いか検討する。
No.176
07/11 13:24

edit

高速化

テーブル名を変更する

テーブル【tb001】を【tb002】にリネームする

alter table tb001 rename to tb002;
No.175
02/10 09:52

edit


データベースで使用する文字コードをutf8に設定する

1. MySQLの使用文字コードを確認する(mysql内で実行する)

status;
または
show variables like '%character%';

テーブル(my_table)の文字コードを確認するには

show create table my_table;

2. /etc/my.cnf に次の項目を追加

[mysqld]
default-character-set=utf8
skip-character-set-client-handshake

[mysql]
default-character-set=utf8

[client]
default-character-set=utf8

3. mysqlをリスタート

/etc/init.d/mysqld restart
または
service mysqld restart

● my.cnf の設定ができない場合は

内部コードがなんであれ、mysqlはクライアント毎に文字コードを設定できる

なので mysql へ接続後

 SET NAMES utf8;

とすると文字コード UTF-8 になる。

なので、流しこむSQL文の先頭にこの1行を追加しておくだけでも良い。

参考:

http://www.mysql.gr.jp/frame/modules/bwiki/index.php?FAQ#dbf81e3d

SET NAMES と SET CHARACTER SETの違い↓

http://dev.mysql.com/doc/refman/4.1/ja/charset-connection.html

No.133
06/21 15:05

edit

件数表示の表記の違い MySQL <-> postgreSQL

■postgreSQL
SELECT * FROM list LIMIT 20 OFFSET 40;

■MySQL
SELECT * FROM list LIMIT 40,20
No.110
08/09 15:04

edit


MySQLユーザの作成

MySQLへの接続ユーザを追加するには以下のコマンドを実行する

grant all privileges on dbname.* to username@hostname identified by 'passname';

username:ユーザ名

dbname:データベース名

hostname:接続元ホスト名

passname:パスワード

以下のコマンドで確認できる

SELECT * from mysql.user; (全て表示)
SELECT Host,User,Password from mysql.user; (簡易表示)
No.96
11/25 09:39

edit


MySQLの文字列の連結演算子

CONCAT

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

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

結果

MySQL
No.90
09/16 20:48

edit


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;
No.11
03/19 22:28

edit


権限の変更

権限の追加(ユーザが存在しないときはユーザ自動作成)

GRANT all 
ON test_db.*
TO sample@localhost
IDENTIFIED BY 'password';

権限の削除

REVOKE ALL 
ON test_db.* 
FROM sample@localhost;

( 該当ユーザは一度 mysqlを終了して抜ける )

GRANT REVOKEマニュアル

http://dev.mysql.com/doc/refman/4.1/ja/grant.html

グローバルレベルの権限の追加 ( 対象(データベース|テーブル)を *.* とする )

GRANT FILE
ON *.*
TO sample@localhost
IDENTIFIED BY 'password';

ユーザの権限を表示

show grants for test_user@localhost;

権限の変更を反映

flush privileges;

No.10
08/23 19:37

edit

MySQL基本コマンド

● 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
No.8
03/01 21:38

edit