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

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コマンドでダンプします。

MySQLのmysqldumpで1行ずつINSERTする

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

-c オプションをつけるとINSERTするカラム名もファイルに書きだされます。

より小さいファイルサイズの方が良い場合はこのオプションを外すといいでしょう

No.898
09/22 20:56

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

文字数(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
11/16 14:54

edit

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

別サーバのmysqlに接続する

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

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

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

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


No.737
06/22 18:50

edit

小数点をもつ数値型 decimal をセットする

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

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

とします。

(10,2)というのは

全部で10桁、小数点2桁

という意味です。

12345678.12
No.701
02/10 09:53

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

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

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

例1:2016年9月8日の00:00 より前(昔)のデータを選択する

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

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

SELECT 
  test_id,
  test_name,
  test_date 
FROM
  test_dt
WHERE
  search_date BETWEEN '2016-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 )

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


No.317
11/17 20:43

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


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

(エンコードUTF-8で運用の場合。)

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

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

mysqlを再起動する。

■2. これでも改善されない場合は文字化けする

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

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

mysql> SET NAMES utf8;

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

No.271
11/12 13:51

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。

※ ヒストリーに パスワードが残ってしまうので、必ずヒストリーを削除すること。

No.251
02/10 09:50

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";

No.180
02/10 10:03

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 【インデックス名】 (【フィールド名】);

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

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クエリーを少しでも早くするには

  • 
    
  • 適切なインデックスをはる(どう貼っていいかわからない時は、全てのカラムにインデックスを貼って EXPLAIN する手もあります。)
  • 文字列後方一致検索をやめる
  • テンポラリテーブルを使ったほうが早いか検討する。
No.176
05/12 11:30

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


文字列の連結演算子

CONCAT

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

SELECT CONCAT( 'My', 'S', 'QL') -> 'MySQL'

参考:
http://www.limy.org/program/db/mysql/mysql_operators.html
No.90
01/16 21:06

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