Linux:MySQLのmax_allowed_packetの値を変更する

LinuxサーバでMySQLmax_allowed_packetの値を変更した際の方法をメモします。

MySQLのmax_allowed_packetで設定している値よりも大きいデータをサーバに送った時に、
エラーになってしまうことがあります。

エラーになってしまう場合は、パケットの最大の値を変更する必要があります。
MySQLでサーバに送るパケットの最大は16MBです。

下記に変更手順を記載します。


MySQLのmax_allowed_packetの値を変更

SSHでサーバにログインし、MySQLに接続します。

mysql -u root -p

最大のパケット値を確認する場合は下記を実行します。

show variables like 'max_allowed_packet';

実行するとmax_allowed_packetの設定値が表示されます。

※1MBの場合

mysql> show variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.00 sec)

max_allowed_packetの値を変更する場合は「my.cnf」を編集します。

vi /etc/my.cnf

max_allowed_packetの値を「16MB」に変更します。

max_allowed_packet=1MB

↓ 変更

max_allowed_packet=16MB

編集を保存し、MySQLを再起動します。

/etc/init.d/mysqld restart

再起動後、MySQLに接続しmax_allowed_packetの値を確認します。

mysql> show variables like 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+
1 row in set (0.00 sec)

max_allowed_packetの値が変更されていればOKです。


MySQLでデータが大き過ぎてエラーが出てしまう場合は、
max_allowed_packetの値を確認すると解決するかもしれないです。

MySQL:ORDER BY句でデータを昇順、降順にソート

MySQLでデータをソートして取得したい場合があります。

ORDER BY句を使用すると取得するデータを指定したフィールドで
昇順降順ソートしながら取得することができます。
昇順でソートする場合は「ASC」を指定し、降順でソートする場合は「DESC」を指定します。

データベースのデータを取得してからプログラムでソートするよりは、
データ取得時にソートして処理する方がいいと思います。

下記にSELECT文ORDER BY句を使用しデータを昇順降順ソートして取得する時の使用方法をメモします。


ORDER BY句でデータをソート(昇順・降順)

※データが下記の場合

MySQL_ORDER_BY_1

・昇順でソートする場合(ASC)

昇順でソートするフィールドにASCを指定します。

SELECT * FROM sample ORDER BY name ASC;

「ASC」を指定しない場合は昇順でソートされます。

SELECT * FROM sample ORDER BY name;

MySQL_ORDER_BY_2

・降順でソートする場合(DESC)

降順でソートするフィールドにDESCを指定します。

SELECT * FROM sample ORDER BY name DESC;

MySQL_ORDER_BY_3


ORDER BYを複数指定してソートする場合

ORDER BYを複数指定する場合は、優先順位の高いものから順番に「,」で区切って指定します。
下記は「name」を昇順ソートし、「id」を降順ソートした場合です。

SELECT * FROM sample ORDER BY name ASC, id DESC;

MySQL_ORDER_BY_4


ORDER BYとLIMITを使用する場合

ORDER BYとLIMITを組み合わせた場合は下記のように記述します。
ORDER BYはLIMITよりも前に記述しないとエラーが起きるので注意です。

SELECT * FROM sample ORDER BY id DESC LIMIT 5;

MySQL_ORDER_BY_5

MySQL:LIMIT句で取得件数を指定してデータ取得

MySQLSELECT文でデータを取得する際に取得する件数指定してデータを取得したい場合があります。

取得する件数を指定する場合はLIMIT句を使用すると件数を指定することができます。
また、OFFSET句を組み合わせるとデータを取得する開始位置を指定することが可能です。

MySQLでデータを取得する時にLIMIT句OFFSET句を使用して、
取得件数開始位置を指定してデータを取得する方法をメモします。


LIMIT句、OFFSET句を使用してデータを取得

※データが下記の場合

mysql_limit_1

LIMIT句で取得する件数を指定します。

SELECT * FROM sample LIMIT 5;

実行すると下記のように指定した件数でデータを取得できます。

mysql_limit_2

次にOFFSET句を使用するとデータを取得する開始位置を指定できます。

SELECT * FROM sample LIMIT 10 OFFSET 5;

実行すると下記のように開始位置から指定した件数のデータを取得できます。

mysql_limit_3

また、OFFSET句は省略して記述することも可能です。

SELECT * FROM sample LIMIT 5, 10;

取得件数と開始位置の記述する順番が逆になるので注意が必要です。


ページネーションを使用しているページのデータを取得・表示する時など、
LIMIT句とOFFSET句を使用すると便利です。

MySQL:MySQLのユーザ追加と削除

MySQLユーザ追加した時の方法をメモします。
サーバを移行する時や別のサーバのデータベースを参照する時などに必要になると思います。

MySQLのユーザはmysqlデータベースの「user」テーブルで管理されています。

下記にMySQLユーザ追加(新規作成)削除する方法をメモします。


MySQLのユーザ追加

SSHでサーバに入り、MySQLにログインします。

$ mysql -u root -p

hogeユーザを追加し、全ての権限を与える場合

mysql> GRANT ALL PRIVILEGES ON *.* TO hoge@"%" IDENTIFIED BY "password" WITH GRANT OPTION;

ユーザ情報を反映させる為に下記を実行します。

mysql> FLUSH PRIVILEGES;

localhostで指定する場合は、下記のように記述します。

mysql> GRANT ALL PRIVILEGES ON *.* TO hoge@"localhost" IDENTIFIED BY "password" WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

IPアドレスで指定する場合は、下記のように記述します。
「xxx.xxx.xxx.xxx」をIPアドレスに書き直します。
別のサーバのMySQLを参照する場合などに使えます。

mysql> GRANT ALL PRIVILEGES ON *.* TO hoge@"xxx.xxx.xxx.xxx" IDENTIFIED BY "password" WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

MySQLのユーザ削除

ユーザを削除する場合はDELETE文で削除します。

mysql> DELETE FROM mysql.user WHERE User="hoge";
mysql> FLUSH PRIVILEGES;

データベースを確認し、削除されていればOKです。


MySQL:MySQLの基本的な構文の書き方

MySQLSQL文を直接書くときがあります。
データベースで直接操作したり、phpなどのシステム内でもSQL文を直接記述し、データを取得する場合があります。

いつも忘れてしますので、MySQL基本的構文の書き方をメモします。


MySQL 構文

SELECT

指定したテーブルのデータを検索、取得します。
*(アスタリスク)で指定した場合、全てのフィールドのデータを取得します。

・全てのフィールドの値を取得する場合

SELECT * FROM テーブル WHERE 条件式 AND 条件式;

・取得するフィールドを指定する場合

SELECT フィールド1, フィールド2, フィールド3 FROM テーブル WHERE 条件式 AND 条件式;

INSERT

指定したテーブルにデータを追加します。

INSERT INTO テーブル (フィールド1, フィールド2, フィールド3) VALUES (フィールド1の値, フィールド2の値, フィールド3の値);

UPDATE

条件式で一致したデータを更新します。

UPDATE テーブル SET フィールド = 値, フィールド = 値 WHERE 条件式 AND 条件式;

DELETE

条件式で一致したデータを削除します。

DELETE FROM テーブル WHERE 条件式 AND 条件式;