MySQL – 文字コード・照合順序

2021-06-10 / tau / コメントする

データベース

確認

データベースの文字コード設定と照合順序設定の確認方法。いずれも対象のデータベースをUSEで使っている状態で実行。

文字コード

SHOW VARIABLES LIKE 'char%';

root@localhost [(none)]> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)

root@localhost [(none)]> SHOW VARIABLES LIKE 'char%';

+--------------------------+----------------------------------+

| Variable_name | Value |

+--------------------------+----------------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | /usr/local/share/mysql/charsets/ |

+--------------------------+----------------------------------+

8 rows in set (0.01 sec)

照合順序

SHOW VARIABLES lIKE 'col%';

root@localhost [(none)]> SHOW VARIABLES LIKE 'col%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.01 sec)

root@localhost [(none)]> SHOW VARIABLES LIKE 'col%';

+----------------------+-----------------+

| Variable_name | Value |

+----------------------+-----------------+

| collation_connection | utf8_general_ci |

| collation_database | utf8_general_ci |

| collation_server | utf8_general_ci |

+----------------------+-----------------+

3 rows in set (0.01 sec)

変数で一度に確認

SELECT @@character_set_database, @@collation_database;

root@localhost [test]> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8                     | utf8_general_ci      |
+--------------------------+----------------------+
1 row in set (0.00 sec)

root@localhost [test]> SELECT @@character_set_database, @@collation_database;

+--------------------------+----------------------+

| @@character_set_database | @@collation_database |

+--------------------------+----------------------+

| utf8 | utf8_general_ci |

+--------------------------+----------------------+

1 row in set (0.00 sec)

変更

ALTER DATABASE test CHARACTER SET 文字コード COLLATE 照合順序;

root@localhost [test]> ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 1 row affected (0.00 sec)

root@localhost [test]> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4                  | utf8mb4_general_ci   |
+--------------------------+----------------------+
1 row in set (0.00 sec)

root@localhost [test]> ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Query OK, 1 row affected (0.00 sec)

root@localhost [test]> SELECT @@character_set_database, @@collation_database;

+--------------------------+----------------------+

| @@character_set_database | @@collation_database |

+--------------------------+----------------------+

| utf8mb4 | utf8mb4_general_ci |

+--------------------------+----------------------+

1 row in set (0.00 sec)

テーブル

確認

SELECT TABLE_NAME, TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='テーブル名';

oot@localhost [test]> SELECT TABLE_NAME, TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test';
+------------+-----------------+
| TABLE_NAME | TABLE_COLLATION |
+------------+-----------------+
| test_table | utf8_general_ci |
+------------+-----------------+
1 row in set (0.01 sec)

oot@localhost [test]> SELECT TABLE_NAME, TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test';

+------------+-----------------+

| TABLE_NAME | TABLE_COLLATION |

+------------+-----------------+

| test_table | utf8_general_ci |

+------------+-----------------+

1 row in set (0.01 sec)

変更

ALTER TABLE test_table CONVERT TO CHARACTER SET 文字コード COLLATE 照合順序;

root@localhost [test]> ALTER TABLE test_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

root@localhost [test]> SELECT TABLE_NAME, TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test';
+------------+--------------------+
| TABLE_NAME | TABLE_COLLATION    |
+------------+--------------------+
| test_table | utf8mb4_general_ci |
+------------+--------------------+
1 row in set (0.01 sec)

root@localhost [test]> ALTER TABLE test_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Query OK, 2 rows affected (0.02 sec)

Records: 2 Duplicates: 0 Warnings: 0

root@localhost [test]> SELECT TABLE_NAME, TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test';

+------------+--------------------+

| TABLE_NAME | TABLE_COLLATION |

+------------+--------------------+

| test_table | utf8mb4_general_ci |

+------------+--------------------+

1 row in set (0.01 sec)

MySQL – 文字コードを変更したのにエラー

2021-06-10 / tau / コメントする

概要

MySQLで日本語入力対策でロケールや文字コードを設定変更しても、なお日本語入力でエラーが出ることがある。

これは、設定変更前にデータベースやテーブルを作成し、後から設定を変更してもcharacter_set_databaseがlatin1のような状態で残ってしまうため。

また、他の環境で作成したデータベースをCSVで取り込もうとしたとき、文字コードを合わせたのにcollation(照合順序)が違うと怒られてインポートできないという場合がある。

ここでは、既作成のデータベースやテーブルの文字コード、照合順序を変更する手順をまとめる。

現象

文字コードを変更したのに入力できない

ロケールも変更して

$ locale
LANG=ja_JP.UTF-8
LC_CTYPE="ja_JP.UTF-8"
LC_COLLATE="ja_JP.UTF-8"
LC_TIME="ja_JP.UTF-8"
LC_NUMERIC="ja_JP.UTF-8"
LC_MONETARY="ja_JP.UTF-8"
LC_MESSAGES="ja_JP.UTF-8"
LC_ALL=ja_JP.UTF-8

$ locale

LANG=ja_JP.UTF-8

LC_CTYPE="ja_JP.UTF-8"

LC_COLLATE="ja_JP.UTF-8"

LC_TIME="ja_JP.UTF-8"

LC_NUMERIC="ja_JP.UTF-8"

LC_MONETARY="ja_JP.UTF-8"

LC_MESSAGES="ja_JP.UTF-8"

LC_ALL=ja_JP.UTF-8

MySQLの文字コードも変更して

root@localhost [(none)]> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)

root@localhost [(none)]> SHOW VARIABLES LIKE 'char%';

+--------------------------+----------------------------------+

| Variable_name | Value |

+--------------------------+----------------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | /usr/local/share/mysql/charsets/ |

+--------------------------+----------------------------------+

8 rows in set (0.01 sec)

再起動もしたのに、日本語が入力できてもデータベースに登録できない。

root@localhost [test]> INSERT INTO test_table (text) VALUES ('やあ');
ERROR 1366 (HY000): Incorrect string value: '\xE6\x97\xA5\xE6\x9C\xAC...' for column 'text' at row 1

1 2	root@localhost [test]> INSERT INTO test_table (text) VALUES ('やあ'); ERROR 1366 (HY000): Incorrect string value: '\xE6\x97\xA5\xE6\x9C\xAC...' for column 'text' at row 1

確認

character_set

MySQLの文字コードを確認するのにUSEでデータベースを指定する前に確認したが、データベース指定後に確認した結果が以下。

root@localhost [test]> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | latin1                           |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)

root@localhost [test]> SHOW VARIABLES LIKE 'char%';

+--------------------------+----------------------------------+

| Variable_name | Value |

+--------------------------+----------------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | latin1 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | /usr/local/share/mysql/charsets/ |

+--------------------------+----------------------------------+

8 rows in set (0.01 sec)

既作成のデータベースでは、作成時の文字コード設定が残っている。

データベースの文字コード

既作成データベースの文字コード確認。文字コードがlatin1になっている。

root@localhost [test]> SHOW CREATE DATABASE test;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [test]> SHOW CREATE DATABASE test;

+----------+---------------------------------------------------------------+

| Database | Create Database |

+----------+---------------------------------------------------------------+

| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ |

+----------+---------------------------------------------------------------+

1 row in set (0.00 sec)

テーブルの文字コード

既作成テーブルの文字コード確認。こちらも文字コードがlatin1になっている。

root@localhost [test]> SHOW CREATE TABLE test_table;
+------------+------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                         |
+------------+------------------------------------------------------------------------------------------------------+
| test_table | CREATE TABLE `test_table` (
  `text` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+------------+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [test]> SHOW CREATE TABLE test_table;

+------------+------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+------------+------------------------------------------------------------------------------------------------------+

| test_table | CREATE TABLE `test_table` (

`text` varchar(20) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+------------+------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

対応

データベースを作りなおす

文字コードがlatin1でまだデータ未入力の段階で発覚したようなときは、一旦dropでデータベースを消して新たに作りなおせば、全体の文字コードに従ったデータベース、テーブルが作成される。

設定を変更する

既にデータが入力されているときに設定を変更したいときは、データベース、テーブルの別に文字コードを変更する。

データベースの文字コードセットを変更する。

root@localhost [test]> ALTER DATABASE test CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)

root@localhost [test]> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)

root@localhost [test]> SHOW CREATE DATABASE test\G
*************************** 1. row ***************************
       Database: test
Create Database: CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)

root@localhost [test]> ALTER DATABASE test CHARACTER SET utf8;

Query OK, 1 row affected (0.00 sec)

root@localhost [test]> SHOW VARIABLES LIKE 'char%';

+--------------------------+----------------------------------+

| Variable_name | Value |

+--------------------------+----------------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | /usr/local/share/mysql/charsets/ |

+--------------------------+----------------------------------+

8 rows in set (0.00 sec)

root@localhost [test]> SHOW CREATE DATABASE test\G

*************************** 1. row ***************************

Database: test

Create Database: CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */

1 row in set (0.00 sec)

テーブルの文字コードセットを変更する。

root@localhost [test]> ALTER TABLE test_table CONVERT TO CHARACTER SET utf8

root@localhost [test]> SHOW CREATE TABLE test_table\G
*************************** 1. row ***************************
       Table: test_table
Create Table: CREATE TABLE `test_table` (
  `text` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

root@localhost [test]> ALTER TABLE test_table CONVERT TO CHARACTER SET utf8

root@localhost [test]> SHOW CREATE TABLE test_table\G

*************************** 1. row ***************************

Table: test_table

Create Table: CREATE TABLE `test_table` (

`text` varchar(20) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

これでやっと日本語の入力のエラーがなくなった。

root@localhost [test]> INSERT INTO test_table (text) VALUES ('やあ');
Query OK, 1 row affected (0.01 sec)

1 2	root@localhost [test]> INSERT INTO test_table (text) VALUES ('やあ'); Query OK, 1 row affected (0.01 sec)

MySQL – 日本語入力ができない

2021-06-10 / tau / コメントする

概要

LinuxのMySQLクライアントからMySQLに対して日本語を入力したとき、以下のような状況となった。

変換中は表示されるが、確定のためEnterを押すと消えてしまう

下記のようにデータベースに登録できない

root@localhost [test]> INSERT INTO test_table (text) VALUES ('やあ');
ERROR 1366 (HY000): Incorrect string value: '\xE6\x97\xA5\xE6\x9C\xAC...' for column 'text' at row 1

1 2	root@localhost [test]> INSERT INTO test_table (text) VALUES ('やあ'); ERROR 1366 (HY000): Incorrect string value: '\xE6\x97\xA5\xE6\x9C\xAC...' for column 'text' at row 1

これを解決するため、OSのロケール設定とMySQLの文字コード設定を変更する。

OSのロケール設定

localeコマンドで現在のロケール設定を確認。デフォルトでCロケールが設定されている。

$ locale
LANG=
LC_CTYPE="C"
LC_COLLATE="C"
LC_TIME="C"
LC_NUMERIC="C"
LC_MONETARY="C"
LC_MESSAGES="C"
LC_ALL=

$ locale

LANG=

LC_CTYPE="C"

LC_COLLATE="C"

LC_TIME="C"

LC_NUMERIC="C"

LC_MONETARY="C"

LC_MESSAGES="C"

LC_ALL=

/etc/profileに以下の2行を追加して再読み込みor再起動。=の前後には空白を入れないこと。

export LC_ALL=ja_JP.UTF-8
export LANG=ja_JP.UTF-8

1 2	export LC_ALL=ja_JP.UTF-8 export LANG=ja_JP.UTF-8

設定変更後。

$ locale
LANG=ja_JP.UTF-8
LC_CTYPE="ja_JP.UTF-8"
LC_COLLATE="ja_JP.UTF-8"
LC_TIME="ja_JP.UTF-8"
LC_NUMERIC="ja_JP.UTF-8"
LC_MONETARY="ja_JP.UTF-8"
LC_MESSAGES="ja_JP.UTF-8"
LC_ALL=ja_JP.UTF-8

$ locale

LANG=ja_JP.UTF-8

LC_CTYPE="ja_JP.UTF-8"

LC_COLLATE="ja_JP.UTF-8"

LC_TIME="ja_JP.UTF-8"

LC_NUMERIC="ja_JP.UTF-8"

LC_MONETARY="ja_JP.UTF-8"

LC_MESSAGES="ja_JP.UTF-8"

LC_ALL=ja_JP.UTF-8

MySQLの文字コード設定

確認方法と指定方法

初期状態のMySQLの文字コードは以下のような状況。

root@localhost [(none)]> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | latin1                           |
| character_set_connection | latin1                           |
| character_set_database   | latin1                           |
| character_set_filesystem | binary                           |
| character_set_results    | latin1                           |
| character_set_server     | latin1                           |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)

root@localhost [(none)]> SHOW VARIABLES LIKE 'character%';

+--------------------------+----------------------------------+

| Variable_name | Value |

+--------------------------+----------------------------------+

| character_set_client | latin1 |

| character_set_connection | latin1 |

| character_set_database | latin1 |

| character_set_filesystem | binary |

| character_set_results | latin1 |

| character_set_server | latin1 |

| character_set_system | utf8 |

| character_sets_dir | /usr/local/share/mysql/charsets/ |

+--------------------------+----------------------------------+

8 rows in set (0.00 sec)

my.cnfのmysqlセクションとmysqldセクションにそれぞれ以下を追加。my.cnfの場所は以下のような方法で特定。

mysql --help | grep my.cnf
find / -name my.sql

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

[mysql]

default-character-set = utf8

[mysqld]

character-set-server = utf8

この結果文字コード設定は以下のようになり、日本語の入力が可能になった。

root@localhost [(none)]> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)

root@localhost [(none)]> 日本語入力が可能になった;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '日本語入力が可能になった' at line 1

root@localhost [(none)]> SHOW VARIABLES LIKE 'char%';

+--------------------------+----------------------------------+

| Variable_name | Value |

+--------------------------+----------------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | /usr/local/share/mysql/charsets/ |

+--------------------------+----------------------------------+

8 rows in set (0.01 sec)

root@localhost [(none)]> 日本語入力が可能になった;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '日本語入力が可能になった' at line 1

コンソールの文字コード

なおこのとき、Windowsホスト上のVagrantをコマンドプロンプトで操作していた。コンソールの文字コードは932(shift_jis)だったが、入力時に文字化けはしなかった。

それでも入力できない

なお、この設定変更をしても、変更前に作成したデータベースに対して入力できない場合がある。

そのようなときは、以下の2つのいずれかの方法をとる。

新たな設定の下で、データベースから作りなおす
データベースとテーブルの設定を個別に変更する

MySQL – 起動しない

2021-06-08 / tau / コメントする

概要

稼働していたMySQLに突然ログインできなくなり、MySQLサーバーも起動できない状態となった。

これに関しては様々な対応策がネット上にあり、症状によって効くものと効かないものがあるようだ。

今回は、単にMySQLを消してインストールし直したところ元に戻った。

環境

仮想環境：Vagrant + VirtualBox

OS：bento/freebsd-11.2

MySQL：5.7をpkgでインストール

症状

VagrantのFreeBSD仮想環境にMySQLをインストールして動作していたのに、翌日ログインしようとしたらエラーになった。

$ mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

$ mysql -u root -p

Enter password:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

導入時にも遭遇した現象で、MySQLサーバーが起動していない。

$ sudo /usr/local/etc/rc.d/mysql-server status
mysql is not running.

1 2	$ sudo /usr/local/etc/rc.d/mysql-server status mysql is not running.

サーバーを起動しようとするとPIDファイルの更新ができないというエラーで起動できない。これはネット上でも頻出の現象だ。

$ sudo /usr/local/share/mysql/mysql.server start
Starting MySQL... ERROR! The server quit without updating PID file (/var/db/mysql/.pid).

1 2	$ sudo /usr/local/share/mysql/mysql.server start Starting MySQL... ERROR! The server quit without updating PID file (/var/db/mysql/.pid).

対応～解決せず

pidファイルが消える

sudo touchで.pidファイルをつくってみたが、MySQLサーバーを起動すると同じエラーとなった。起動後に確認すると、つくった.pidファイルがなくなっていた。

プロセスはない

mysql関係のプロセスを見てみたが、実行したgrep以外には動いていない。

$ ps aux | grep mysql
vagrant 4949   0.0  0.3 11872 2896  0  R+   09:57    0:00.00 grep mysql

1 2	$ ps aux \| grep mysql vagrant 4949 0.0 0.3 11872 2896 0 R+ 09:57 0:00.00 grep mysql

pkg clean

/var/chache/pkg下の40ほどのtxzファイルが消されたが、PIDファイルが更新できない、というエラーは変わらない。

対応～解決

MySQLサーバーをアンインストールして、my.cnfを消す。

$ sudo pkg delete mysql57-server
Checking integrity... done (0 conflicting)
Deinstallation has been requested for the following 1 packages (of 0 packages in the universe):

Installed packages to be REMOVED:
        mysql57-server: 5.7.33

Number of packages to be removed: 1

The operation will free 138 MiB.

Proceed with deinstalling packages? [y/N]: y
[1/1] Deinstalling mysql57-server-5.7.33...
[1/1] Deleting files for mysql57-server-5.7.33: 100%
==> You should manually remove the "mysql" user.
==> You should manually remove the "mysql" group
You may need to manually remove /usr/local/etc/mysql/my.cnf if it is no longer needed.
$ sudo rm /usr/local/etc/mysql/my.cnf

$ sudo pkg delete mysql57-server

Checking integrity... done (0 conflicting)

Deinstallation has been requested for the following 1 packages (of 0 packages in the universe):

Installed packages to be REMOVED:

mysql57-server: 5.7.33

Number of packages to be removed: 1

The operation will free 138 MiB.

Proceed with deinstalling packages? [y/N]: y

[1/1] Deinstalling mysql57-server-5.7.33...

[1/1] Deleting files for mysql57-server-5.7.33: 100%

==> You should manually remove the "mysql" user.

==> You should manually remove the "mysql" group

You may need to manually remove /usr/local/etc/mysql/my.cnf if it is no longer needed.

$ sudo rm /usr/local/etc/mysql/my.cnf

VagrantをリロードしてMySQLサーバーを再インストール。

$ sudo pkg install mysql57-server
Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
All repositories are up to date.
The following 1 package(s) will be affected (of 0 checked):

New packages to be INSTALLED:
        mysql57-server: 5.7.33

Number of packages to be installed: 1

The process will require 138 MiB more space.
14 MiB to be downloaded.

Proceed with this action? [y/N]: y
[1/1] Fetching mysql57-server-5.7.33.txz: 100%   14 MiB   3.6MB/s    00:04
Checking integrity... done (0 conflicting)
[1/1] Installing mysql57-server-5.7.33...
===> Creating groups.
Using existing group 'mysql'.
===> Creating users
Using existing user 'mysql'.
===> Creating homedir(s)
[1/1] Extracting mysql57-server-5.7.33: 100%
=====
Message from mysql57-server-5.7.33:

--
Initial password for first time use of MySQL is saved in $HOME/.mysql_secret
ie. when you want to use "mysql -u root -p" first you should see password
in /root/.mysql_secret

MySQL57 has a default /usr/local/etc/mysql/my.cnf,
remember to replace it wit your own
or set `mysql_optfile="$YOUR_CNF_FILE` in rc.conf.

$ sudo pkg install mysql57-server

Updating FreeBSD repository catalogue...

FreeBSD repository is up to date.

All repositories are up to date.

The following 1 package(s) will be affected (of 0 checked):

New packages to be INSTALLED:

mysql57-server: 5.7.33

Number of packages to be installed: 1

The process will require 138 MiB more space.

14 MiB to be downloaded.

Proceed with this action? [y/N]: y

[1/1] Fetching mysql57-server-5.7.33.txz: 100% 14 MiB 3.6MB/s 00:04

Checking integrity... done (0 conflicting)

[1/1] Installing mysql57-server-5.7.33...

===> Creating groups.

Using existing group 'mysql'.

===> Creating users

Using existing user 'mysql'.

===> Creating homedir(s)

[1/1] Extracting mysql57-server-5.7.33: 100%

=====

Message from mysql57-server-5.7.33:

Initial password for first time use of MySQL is saved in $HOME/.mysql_secret

ie. when you want to use "mysql -u root -p" first you should see password

in /root/.mysql_secret

MySQL57 has a default /usr/local/etc/mysql/my.cnf,

remember to replace it wit your own

or set `mysql_optfile="$YOUR_CNF_FILE` in rc.conf.

この段階ではMySQLサーバーは動いていないが、無事起動に成功。

$ /usr/local/etc/rc.d/mysql-server status
mysql is not running.
$ sudo /usr/local/etc/rc.d/mysql-server start
Starting mysql.
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.33-log Source distribution
.....

$ /usr/local/etc/rc.d/mysql-server status

mysql is not running.

$ sudo /usr/local/etc/rc.d/mysql-server start

Starting mysql.

$ mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.7.33-log Source distribution

.....

また、

前に作ったデータベースは残っていた
そのままexit→Vagrantリロードすると、サーバーが起動(profileにmysql_enable="YES"を記述しているため)

MySQL – ログインとパスワード変更

2021-06-06 / tau / コメントする

$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.34-log Source distribution

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]> show databases;

$ mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 6

Server version: 5.7.34-log Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]> show databases;

パスワード変更

OSプロンプトから

OSのプロンプトに対して、以下のコマンド入力後に変更前のパスワードを入力。

mysqladmin password new_password -u root -p

たとえばrootのパスワードを変更する場合。”Enter password:”に対しては変更前のパスワードを入力する。

$ mysqladmin password ****** -u root -p
Enter password:
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

$ mysqladmin password ****** -u root -p

Enter password:

mysqladmin: [Warning] Using a password on the command line interface can be insecure.

Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

MySQLログイン中

MySQLのプロンプトで以下のコマンドを入力。

SET PASSWORD FOR user@host=password('new_password');

たとえばrootのパスワードを変更する場合。

root@localhost [(none)]> SET PASSWORD FOR root@localhost=password('******');
Query OK, 0 rows affected, 1 warning (0.00 sec)

1 2	root@localhost [(none)]> SET PASSWORD FOR root@localhost=password('******'); Query OK, 0 rows affected, 1 warning (0.00 sec)

MySQL – CSVファイルのインポートでエラー

2021-06-01 / tau / 1件のコメント

概要

MySQLでデータベースにCSVファイルをインポートしようとしたときに以下のようなエラーが出た。

mysql> LOAD DATA LOCAL INFILE "~/share/rounds.csv"
    -> INTO TABLE rounds FIELDS TERMINATED BY ',';
ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

mysql> LOAD DATA LOCAL INFILE "~/share/rounds.csv"

-> INTO TABLE rounds FIELDS TERMINATED BY ',';

ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

MySQLのバージョンが新しいと出るらしく、メッセージにあるようにサーバーとクライアントの双方で許可を与える必要があるそうだ。

手順

クライアント側の指定

mysqlのログイン時にオプションでローカルの入力ファイルを許可する。オプションの書き方は以下2通りのいずれか

--enable-local-infile
--local_infile=1またはon

$ mysql -u root -p --enable-local-infile

1	$ mysql -u root -p --enable-local-infile

これを設定しておかないと、後述のグローバルパラメーターを有効にしても、LOAD DATA実行時に以下のようなエラーが出る。

ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

1	ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

サーバー側の指定

MySQLにログイン後、以下のコマンドでサーバーのlocal_infileの設定を確認すると0(無効)になっている。

mysql> select @@local_infile;
+----------------+
| @@local_infile |
+----------------+
|              0 |
+----------------+
1 row in set (0.00 sec)

mysql> select @@local_infile;

+----------------+

| @@local_infile |

+----------------+

| 0 |

+----------------+

1 row in set (0.00 sec)

local_infileを有効にして、設定が反映されていることを確認。

mysql> SET GLOBAL local_infile=on;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@local_infile;
+----------------+
| @@local_infile |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

mysql> SET GLOBAL local_infile=on;

Query OK, 0 rows affected (0.00 sec)

mysql> select @@local_infile;

+----------------+

| @@local_infile |

+----------------+

| 1 |

+----------------+

1 row in set (0.00 sec)

読み込み成功

mysql> LOAD DATA LOCAL INFILE "~/share/rounds.csv"
    -> INTO TABLE rounds
    -> FIELDS TERMINATED BY ','
    -> LINES TERMINATED BY "\r\n";
Query OK, 1 row affected (0.01 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> LOAD DATA LOCAL INFILE "~/share/rounds.csv"

-> INTO TABLE rounds

-> FIELDS TERMINATED BY ','

-> LINES TERMINATED BY "\r\n";

Query OK, 1 row affected (0.01 sec)

Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

MySQL – 日付・時刻のフォーマット

2021-02-23 / tau / コメントする

概要

日付、時刻のフォーマットにはDATE_FORMAT()、TIME_FORMAT()を使う。

DATE_FORMAT(date, format);

TIME_FORMAT(time, format);

date、timeにはDATETIME型の値を入れてもよい。

書式の雛形

以下のデータで日付・時刻の書式を確認する。

mysql> SELECT * FROM test_datetime;
+---------------------+
| datetime            |
+---------------------+
| 2020-12-31 23:59:59 |
| 2021-01-01 00:01:02 |
+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test_datetime;

+---------------------+

| datetime |

+---------------------+

| 2020-12-31 23:59:59 |

| 2021-01-01 00:01:02 |

+---------------------+

2 rows in set (0.00 sec)

日付

日付4or2桁でゼロ埋め	`%Y %m %d`
日付ゼロ埋めなし	`%y %c %e`
月名(短縮／全部)	`%b / %M`
曜日名(番号／短縮／全部)	`%w / %a / %W`

※曜日番号は日曜～土曜が0～6

mysql> SELECT
    ->   DATE_FORMAT(datetime, '%Y年%m月%d日') AS '日付0埋め',
    ->   DATE_FORMAT(datetime, '%y年%c月%e日') AS '日付0なし',
    ->   DATE_FORMAT(datetime, '%b : %M') AS 月名,
    ->   DATE_FORMAT(datetime, '%w : %a : %W') AS 曜日
    -> FROM test_datetime;
+----------------+--------------+----------------+--------------------+
| 日付0埋め      | 日付0なし    | 月名           | 曜日               |
+----------------+--------------+----------------+--------------------+
| 2020年12月31日 | 20年12月31日 | Dec : December | 4 : Thu : Thursday |
| 2021年01月01日 | 21年1月1日   | Jan : January  | 5 : Fri : Friday   |
+----------------+--------------+----------------+--------------------+
2 rows in set (0.00 sec)

mysql> SELECT

-> DATE_FORMAT(datetime, '%Y年%m月%d日') AS '日付0埋め',

-> DATE_FORMAT(datetime, '%y年%c月%e日') AS '日付0なし',

-> DATE_FORMAT(datetime, '%b : %M') AS 月名,

-> DATE_FORMAT(datetime, '%w : %a : %W') AS 曜日

-> FROM test_datetime;

+----------------+--------------+----------------+--------------------+

| 日付0埋め | 日付0なし | 月名 | 曜日 |

+----------------+--------------+----------------+--------------------+

| 2020年12月31日 | 20年12月31日 | Dec : December | 4 : Thu : Thursday |

| 2021年01月01日 | 21年1月1日 | Jan : January | 5 : Fri : Friday |

+----------------+--------------+----------------+--------------------+

2 rows in set (0.00 sec)

時刻

24時間制の時刻(ゼロ埋め)	%H
24時間制の時刻(ゼロ埋めなし)	%k
12時間制の時刻	%h
分(ゼロ埋め)	%i
秒(ゼロ埋め)	%s
AM/PM	`%p`

※12時間制の時刻は0時／12時の扱いに一貫性がない。

mysql> SELECT
    ->   TIME_FORMAT(datetime, '%H時%i分%s秒') AS '0埋め24H',
    ->   TIME_FORMAT(datetime, '%k時%m分%s秒') AS '0なし24H',
    ->   TIME_FORMAT(datetime, '%h時%i分%S秒 %p') AS '0埋め12H',
    ->   TIME_FORMAT(datetime, '%l時%i分%S秒 %p') AS '0なし12H'
    -> FROM test_datetime;
+--------------+--------------+-----------------+-----------------+
| 0埋め24H     | 0なし24H     | 0埋め12H        | 0なし12H        |
+--------------+--------------+-----------------+-----------------+
| 23時59分59秒 | 23時00分59秒 | 11時59分59秒 PM | 11時59分59秒 PM |
| 00時01分02秒 | 0時00分02秒  | 12時01分02秒 AM | 12時01分02秒 AM |
+--------------+--------------+-----------------+-----------------+
2 rows in set (0.00 sec)

mysql> SELECT

-> TIME_FORMAT(datetime, '%H時%i分%s秒') AS '0埋め24H',

-> TIME_FORMAT(datetime, '%k時%m分%s秒') AS '0なし24H',

-> TIME_FORMAT(datetime, '%h時%i分%S秒 %p') AS '0埋め12H',

-> TIME_FORMAT(datetime, '%l時%i分%S秒 %p') AS '0なし12H'

-> FROM test_datetime;

+--------------+--------------+-----------------+-----------------+

| 0埋め24H | 0なし24H | 0埋め12H | 0なし12H |

+--------------+--------------+-----------------+-----------------+

| 23時59分59秒 | 23時00分59秒 | 11時59分59秒 PM | 11時59分59秒 PM |

| 00時01分02秒 | 0時00分02秒 | 12時01分02秒 AM | 12時01分02秒 AM |

+--------------+--------------+-----------------+-----------------+

2 rows in set (0.00 sec)

MySQL – DISTINCTによる重複の排除

2021-02-23 / tau / コメントする

概要

DISTINCTはフィールドの重複を除いた結果を返す。

確認に使用するデータ

以下のデータを使う。日時ごとのパスタのメニューの注文票で、パスタはソースと麺の種類の組み合わせ。

mysql> SELECT * FROM pasta_order;
+---------------------+----------+----------------+-------+
| order_time          | source   | pasta          | price |
+---------------------+----------+----------------+-------+
| 2021-02-01 10:30:00 | トマト   | スパゲッティー |   900 |
| 2021-02-01 11:30:00 | トマト   | フィットチーネ |   900 |
| 2021-02-01 12:00:00 | オイル   | スパゲッティー |   800 |
| 2021-02-01 12:30:00 | クリーム | リングイネ     |   850 |
| 2021-02-01 13:00:00 | トマト   | リングイネ     |   900 |
| 2021-02-02 11:00:00 | トマト   | スパゲッティー |   900 |
| 2021-02-02 12:00:00 | クリーム | スパゲッティー |   850 |
| 2021-02-02 12:30:00 | トマト   | リングイネ     |   900 |
| 2021-02-02 13:00:00 | トマト   | スパゲッティー |   900 |
| 2021-02-03 10:30:00 | クリーム | スパゲッティー |   850 |
| 2021-02-03 11:00:00 | オイル   | スパゲッティー |   800 |
| 2021-02-03 11:30:00 | オイル   | フィットチーネ |   800 |
| 2021-02-03 12:00:00 | クリーム | リングイネ     |   850 |
| 2021-02-03 12:30:00 | トマト   | フィットチーネ |   900 |
| 2021-02-03 13:00:00 | オイル   | スパゲッティー |   800 |
+---------------------+----------+----------------+-------+
15 rows in set (0.00 sec)

mysql> SELECT * FROM pasta_order;

+---------------------+----------+----------------+-------+

+---------------------+----------+----------------+-------+

| 2021-02-01 10:30:00 | トマト | スパゲッティー | 900 |

| 2021-02-01 11:30:00 | トマト | フィットチーネ | 900 |

| 2021-02-01 12:00:00 | オイル | スパゲッティー | 800 |

| 2021-02-01 12:30:00 | クリーム | リングイネ | 850 |

| 2021-02-01 13:00:00 | トマト | リングイネ | 900 |

| 2021-02-02 11:00:00 | トマト | スパゲッティー | 900 |

| 2021-02-02 12:00:00 | クリーム | スパゲッティー | 850 |

| 2021-02-02 12:30:00 | トマト | リングイネ | 900 |

| 2021-02-02 13:00:00 | トマト | スパゲッティー | 900 |

| 2021-02-03 10:30:00 | クリーム | スパゲッティー | 850 |

| 2021-02-03 11:00:00 | オイル | スパゲッティー | 800 |

| 2021-02-03 11:30:00 | オイル | フィットチーネ | 800 |

| 2021-02-03 12:00:00 | クリーム | リングイネ | 850 |

| 2021-02-03 12:30:00 | トマト | フィットチーネ | 900 |

| 2021-02-03 13:00:00 | オイル | スパゲッティー | 800 |

+---------------------+----------+----------------+-------+

15 rows in set (0.00 sec)

フィールドの重複を除いた抽出

まずソース、麺それぞれのフィールドから重複を除いた結果を抽出。ソースの種類、麺の種類は3種類のため、それぞれ3つのレコードが返される。

mysql> SELECT
    ->   DISTINCT source
    -> FROM pasta_order;
+----------+
| source   |
+----------+
| トマト   |
| オイル   |
| クリーム |
+----------+
3 rows in set (0.00 sec)

mysql> SELECT
    ->   DISTINCT pasta
    -> FROM pasta_order;
+----------------+
| pasta          |
+----------------+
| スパゲッティー |
| フィットチーネ |
| リングイネ     |
+----------------+
3 rows in set (0.00 sec)

mysql> SELECT

-> DISTINCT source

-> FROM pasta_order;

+----------+

| source |

+----------+

| トマト |

| オイル |

| クリーム |

+----------+

3 rows in set (0.00 sec)

mysql> SELECT

-> DISTINCT pasta

-> FROM pasta_order;

+----------------+

| pasta |

+----------------+

| スパゲッティー |

| フィットチーネ |

| リングイネ |

+----------------+

3 rows in set (0.00 sec)

日付による集計

日付単位の表示

DATETIMEで登録されているフィールドを日付のみの表現に変更する。その結果、同じ日付で複数のソースと麺の組み合わせのレコードになる。

mysql> SELECT
    ->   DATE_FORMAT(order_time, '%Y-%m-%d') AS date,
    ->   source,
    ->   pasta
    -> FROM pasta_order;
+------------+----------+----------------+
| date       | source   | pasta          |
+------------+----------+----------------+
| 2021-02-01 | トマト   | スパゲッティー |
| 2021-02-01 | トマト   | フィットチーネ |
| 2021-02-01 | オイル   | スパゲッティー |
| 2021-02-01 | クリーム | リングイネ     |
| 2021-02-01 | トマト   | リングイネ     |
| 2021-02-02 | トマト   | スパゲッティー |
| 2021-02-02 | クリーム | スパゲッティー |
| 2021-02-02 | トマト   | リングイネ     |
| 2021-02-02 | トマト   | スパゲッティー |
| 2021-02-03 | クリーム | スパゲッティー |
| 2021-02-03 | オイル   | スパゲッティー |
| 2021-02-03 | オイル   | フィットチーネ |
| 2021-02-03 | クリーム | リングイネ     |
| 2021-02-03 | トマト   | フィットチーネ |
| 2021-02-03 | オイル   | スパゲッティー |
+------------+----------+----------------+
15 rows in set (0.00 sec)

mysql> SELECT

-> DATE_FORMAT(order_time, '%Y-%m-%d') AS date,

-> source,

-> pasta

-> FROM pasta_order;

+------------+----------+----------------+

| date | source | pasta |

+------------+----------+----------------+

| 2021-02-01 | トマト | スパゲッティー |

| 2021-02-01 | トマト | フィットチーネ |

| 2021-02-01 | オイル | スパゲッティー |

| 2021-02-01 | クリーム | リングイネ |

| 2021-02-01 | トマト | リングイネ |

| 2021-02-02 | トマト | スパゲッティー |

| 2021-02-02 | クリーム | スパゲッティー |

| 2021-02-02 | トマト | リングイネ |

| 2021-02-02 | トマト | スパゲッティー |

| 2021-02-03 | クリーム | スパゲッティー |

| 2021-02-03 | オイル | スパゲッティー |

| 2021-02-03 | オイル | フィットチーネ |

| 2021-02-03 | クリーム | リングイネ |

| 2021-02-03 | トマト | フィットチーネ |

| 2021-02-03 | オイル | スパゲッティー |

+------------+----------+----------------+

15 rows in set (0.00 sec)

日付ごとのユニークデータの数

単一のフィールド

単一フィールド中のユニークなデータの数は以下のようにしてカウントする。

COUNT(DISTINCT col)

以下は、日付ごとにグルーピングし、各日付で重複を除いたソースの数をカウントしている。2/2はクリームとトマトの2種類だけというのが反映されている。

mysql> SELECT
    ->   DATE_FORMAT(order_time, '%Y-%m-%d') AS date,
    ->   COUNT(DISTINCT source)
    -> FROM pasta_order
    -> GROUP BY date;
+------------+------------------------+
| date       | COUNT(DISTINCT source) |
+------------+------------------------+
| 2021-02-01 |                      3 |
| 2021-02-02 |                      2 |
| 2021-02-03 |                      3 |
+------------+------------------------+
3 rows in set (0.00 sec)

mysql> SELECT

-> DATE_FORMAT(order_time, '%Y-%m-%d') AS date,

-> COUNT(DISTINCT source)

-> FROM pasta_order

-> GROUP BY date;

+------------+------------------------+

| date | COUNT(DISTINCT source) |

+------------+------------------------+

| 2021-02-01 | 3 |

| 2021-02-02 | 2 |

| 2021-02-03 | 3 |

+------------+------------------------+

3 rows in set (0.00 sec)

また、日付ごとの麺の種類の数もカウントしてみる。2/2がスパゲッティーとリングイネの2種類になっている。

mysql> SELECT
    ->   DATE_FORMAT(order_time, '%Y-%m-%d') AS date,
    ->   COUNT(DISTINCT pasta)
    -> FROM pasta_order
    -> GROUP BY date;
+------------+-----------------------+
| date       | COUNT(DISTINCT pasta) |
+------------+-----------------------+
| 2021-02-01 |                     3 |
| 2021-02-02 |                     2 |
| 2021-02-03 |                     3 |
+------------+-----------------------+
3 rows in set (0.00 sec)

mysql> SELECT

-> DATE_FORMAT(order_time, '%Y-%m-%d') AS date,

-> COUNT(DISTINCT pasta)

-> FROM pasta_order

-> GROUP BY date;

+------------+-----------------------+

| date | COUNT(DISTINCT pasta) |

+------------+-----------------------+

| 2021-02-01 | 3 |

| 2021-02-02 | 2 |

| 2021-02-03 | 3 |

+------------+-----------------------+

3 rows in set (0.00 sec)

複数フィールド

複数フィールドの組み合わせがユニークなものの数のカウントは以下のようにする。

COUNT(DISTINCT CONCAT(col1, col2, ...))

以下の例では、ソースとパスタの組み合わせがユニークなものの数を日付ごとに集計する。2/2はトマトスパゲッティーが2回、2/3はオイルスパゲッティーがあり、重複を除いてカウントしている。

mysql> SELECT
    ->   DATE_FORMAT(order_time, '%Y-%m-%d') AS date,
    ->   COUNT(DISTINCT CONCAT(source, pasta))
    -> FROM pasta_order
    -> GROUP BY date;
+------------+---------------------------------------+
| date       | COUNT(DISTINCT CONCAT(source, pasta)) |
+------------+---------------------------------------+
| 2021-02-01 |                                     5 |
| 2021-02-02 |                                     3 |
| 2021-02-03 |                                     5 |
+------------+---------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT

-> DATE_FORMAT(order_time, '%Y-%m-%d') AS date,

-> COUNT(DISTINCT CONCAT(source, pasta))

-> FROM pasta_order

-> GROUP BY date;

+------------+---------------------------------------+

| date | COUNT(DISTINCT CONCAT(source, pasta)) |

+------------+---------------------------------------+

| 2021-02-01 | 5 |

| 2021-02-02 | 3 |

| 2021-02-03 | 5 |

+------------+---------------------------------------+

3 rows in set (0.00 sec)

MySQL – グループごとの最大値のレコード

2021-02-23 / tau / コメントする

概要

インラインビューとグルーピングを使って、レコード中のある列の値が最大値／最小値となるレコードをグループごとに抽出できる。

以下、実行例に沿ってその方法を整理する。

実行例

以下のデータを使う。

mysql> SELECT * FROM student_scores;
+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 安藤 | 数学    |    80 |
| 安藤 | 物理    |    90 |
| 安藤 | 英語    |    75 |
| 伊藤 | 数学    |    65 |
| 伊藤 | 文学    |    85 |
| 宇藤 | 物理    |    70 |
| 宇藤 | 英語    |    85 |
| 宇藤 | 文学    |    95 |
| 江藤 | 数学    |    85 |
| 江藤 | 物理    |    80 |
| 江藤 | 文学    |    65 |
+------+---------+-------+
11 rows in set (0.00 sec)

mysql> SELECT * FROM student_scores;

+------+---------+-------+

| name | subject | score |

+------+---------+-------+

| 安藤 | 数学 | 80 |

| 安藤 | 物理 | 90 |

| 安藤 | 英語 | 75 |

| 伊藤 | 数学 | 65 |

| 伊藤 | 文学 | 85 |

| 宇藤 | 物理 | 70 |

| 宇藤 | 英語 | 85 |

| 宇藤 | 文学 | 95 |

| 江藤 | 数学 | 85 |

| 江藤 | 物理 | 80 |

| 江藤 | 文学 | 65 |

+------+---------+-------+

11 rows in set (0.00 sec)

ここでは各科目ごとの最高点をとった学生と教科を抽出する。

SELECT name, subject, score, max_sub, max_scr
FROM
  student_scores,
  (
    SELECT subject AS max_sub, MAX(score) AS max_scr
    FROM student_scores
    GROUP BY subject
  ) AS max_table
WHERE subject = max_sub AND score = max_scr
;

SELECT name, subject, score, max_sub, max_scr

FROM

student_scores,

(

SELECT subject AS max_sub, MAX(score) AS max_scr

FROM student_scores

GROUP BY subject

) AS max_table

WHERE subject = max_sub AND score = max_scr

;

+------+---------+-------+---------+---------+
| name | subject | score | max_sub | max_scr |
+------+---------+-------+---------+---------+
| 安藤 | 物理    |    90 | 物理    |      90 |
| 宇藤 | 英語    |    85 | 英語    |      85 |
| 宇藤 | 文学    |    95 | 文学    |      95 |
| 江藤 | 数学    |    85 | 数学    |      85 |
+------+---------+-------+---------+---------+
4 rows in set (0.00 sec)

+------+---------+-------+---------+---------+

+------+---------+-------+---------+---------+

| 安藤 | 物理 | 90 | 物理 | 90 |

| 宇藤 | 英語 | 85 | 英語 | 85 |

| 宇藤 | 文学 | 95 | 文学 | 95 |

| 江藤 | 数学 | 85 | 数学 | 85 |

+------+---------+-------+---------+---------+

4 rows in set (0.00 sec)

手順

グループごとの最大値

科目によってグルーピングし、各科目の最高得点を得る。

SELECT subject, MAX(score) FROM student_scores GROUP BY subject;

1	SELECT subject, MAX(score) FROM student_scores GROUP BY subject;

mysql> SELECT subject, MAX(score) FROM student_scores GROUP BY subject;
+---------+------------+
| subject | MAX(score) |
+---------+------------+
| 数学    |         85 |
| 物理    |         90 |
| 英語    |         85 |
| 文学    |         95 |
+---------+------------+
4 rows in set (0.00 sec)

mysql> SELECT subject, MAX(score) FROM student_scores GROUP BY subject;

+---------+------------+

| subject | MAX(score) |

+---------+------------+

| 数学 | 85 |

| 物理 | 90 |

| 英語 | 85 |

| 文学 | 95 |

+---------+------------+

4 rows in set (0.00 sec)

インラインビューによる最高得点列の追加

インラインビューに別名を定義し、元のテーブルと併記することで、全レコードに新たな最高スコアの列が追加される。

SELECT name, subject, max_sub, max_scr
FROM
  student_scores,
  (
    SELECT subject AS max_sub, MAX(score) AS max_scr
    FROM student_scores
    GROUP BY subject
  ) AS max_table
;

SELECT name, subject, max_sub, max_scr

FROM

student_scores,

(

SELECT subject AS max_sub, MAX(score) AS max_scr

FROM student_scores

GROUP BY subject

) AS max_table

;

mysql> SELECT name, subject, max_sub, max_scr
    -> FROM
    ->   student_scores,
    ->   (
    ->     SELECT subject AS max_sub, MAX(score) AS max_scr
    ->     FROM student_scores
    ->     GROUP BY subject
    ->   ) AS max_table
    -> ;
+------+---------+---------+---------+
| name | subject | max_sub | max_scr |
+------+---------+---------+---------+
| 江藤 | 文学    | 数学    |      85 |
| 江藤 | 物理    | 数学    |      85 |
| 江藤 | 数学    | 数学    |      85 |
| 宇藤 | 文学    | 数学    |      85 |
| 宇藤 | 英語    | 数学    |      85 |
| 宇藤 | 物理    | 数学    |      85 |
| 伊藤 | 文学    | 数学    |      85 |
| 伊藤 | 数学    | 数学    |      85 |
| 安藤 | 英語    | 数学    |      85 |
| 安藤 | 物理    | 数学    |      85 |
| 安藤 | 数学    | 数学    |      85 |
| 江藤 | 文学    | 物理    |      90 |
| 江藤 | 物理    | 物理    |      90 |
| 江藤 | 数学    | 物理    |      90 |
| 宇藤 | 文学    | 物理    |      90 |
.....

mysql> SELECT name, subject, max_sub, max_scr

-> FROM

-> student_scores,

-> (

-> SELECT subject AS max_sub, MAX(score) AS max_scr

-> FROM student_scores

-> GROUP BY subject

-> ) AS max_table

-> ;

+------+---------+---------+---------+

+------+---------+---------+---------+

| 江藤 | 文学 | 数学 | 85 |

| 江藤 | 物理 | 数学 | 85 |

| 江藤 | 数学 | 数学 | 85 |

| 宇藤 | 文学 | 数学 | 85 |

| 宇藤 | 英語 | 数学 | 85 |

| 宇藤 | 物理 | 数学 | 85 |

| 伊藤 | 文学 | 数学 | 85 |

| 伊藤 | 数学 | 数学 | 85 |

| 安藤 | 英語 | 数学 | 85 |

| 安藤 | 物理 | 数学 | 85 |

| 安藤 | 数学 | 数学 | 85 |

| 江藤 | 文学 | 物理 | 90 |

| 江藤 | 物理 | 物理 | 90 |

| 江藤 | 数学 | 物理 | 90 |

| 宇藤 | 文学 | 物理 | 90 |

.....

最高得点のレコードの追加

各学生・科目のレコードのうち、スコアが最高スコアに等しいレコードを抽出する。

SELECT name, subject, score, max_sub, max_scr
FROM
  student_scores,
  (
    SELECT subject AS max_sub, MAX(score) AS max_scr
    FROM student_scores
    GROUP BY subject
  ) AS max_table
WHERE subject = max_sub AND score = max_scr
;

SELECT name, subject, score, max_sub, max_scr

FROM

student_scores,

(

SELECT subject AS max_sub, MAX(score) AS max_scr

FROM student_scores

GROUP BY subject

) AS max_table

WHERE subject = max_sub AND score = max_scr

;

mysql> SELECT name, subject, score, max_sub, max_scr
    -> FROM
    ->   student_scores,
    ->   (
    ->     SELECT subject AS max_sub, MAX(score) AS max_scr
    ->     FROM student_scores
    ->     GROUP BY subject
    ->   ) AS max_table
    -> WHERE subject = max_sub AND score = max_scr
    -> ;
+------+---------+-------+---------+---------+
| name | subject | score | max_sub | max_scr |
+------+---------+-------+---------+---------+
| 安藤 | 物理    |    90 | 物理    |      90 |
| 宇藤 | 英語    |    85 | 英語    |      85 |
| 宇藤 | 文学    |    95 | 文学    |      95 |
| 江藤 | 数学    |    85 | 数学    |      85 |
+------+---------+-------+---------+---------+
4 rows in set (0.00 sec)

mysql> SELECT name, subject, score, max_sub, max_scr

-> FROM

-> student_scores,

-> (

-> SELECT subject AS max_sub, MAX(score) AS max_scr

-> FROM student_scores

-> GROUP BY subject

-> ) AS max_table

-> WHERE subject = max_sub AND score = max_scr

-> ;

+------+---------+-------+---------+---------+

+------+---------+-------+---------+---------+

| 安藤 | 物理 | 90 | 物理 | 90 |

| 宇藤 | 英語 | 85 | 英語 | 85 |

| 宇藤 | 文学 | 95 | 文学 | 95 |

| 江藤 | 数学 | 85 | 数学 | 85 |

+------+---------+-------+---------+---------+

4 rows in set (0.00 sec)

MySQL – シェアの計算

2021-02-23 / tau / コメントする

概要

インラインビューを使って、各レコードのある列の値のシェアを計算できる。

以下、実行例に沿ってその方法を整理する。

実行例

以下のデータを使う。

mysql> SELECT * FROM components;
+-----------+----------+
| component | quantity |
+-----------+----------+
| 成分A     |       60 |
| 成分B     |       35 |
| 成分C     |       15 |
+-----------+----------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM components;

+-----------+----------+

| component | quantity |

+-----------+----------+

| 成分A | 60 |

| 成分B | 35 |

| 成分C | 15 |

+-----------+----------+

3 rows in set (0.00 sec)

ここでは各成分の量quantityのシェアを計算する。

SELECT
  component,
  quantity,
  total,
  quantity / total AS rate
FROM
  components,
  (SELECT SUM(quantity) AS total FROM components) AS view
;

SELECT

component,

quantity,

total,

quantity / total AS rate

FROM

components,

(SELECT SUM(quantity) AS total FROM components) AS view

;

mysql> SELECT
    ->   component,
    ->   quantity,
    ->   total,
    ->   quantity / total AS rate
    -> FROM
    ->   components,
    ->   (SELECT SUM(quantity) AS total FROM components) AS view
    -> ;
+-----------+----------+-------+--------+
| component | quantity | total | rate   |
+-----------+----------+-------+--------+
| 成分A     |       60 |   110 | 0.5455 |
| 成分B     |       35 |   110 | 0.3182 |
| 成分C     |       15 |   110 | 0.1364 |
+-----------+----------+-------+--------+
3 rows in set (0.00 sec)

mysql> SELECT

-> component,

-> quantity,

-> total,

-> quantity / total AS rate

-> FROM

-> components,

-> (SELECT SUM(quantity) AS total FROM components) AS view

-> ;

+-----------+----------+-------+--------+

+-----------+----------+-------+--------+

| 成分A | 60 | 110 | 0.5455 |

| 成分B | 35 | 110 | 0.3182 |

| 成分C | 15 | 110 | 0.1364 |

+-----------+----------+-------+--------+

3 rows in set (0.00 sec)

手順

合計値の計算

合計値はSUM()関数で計算。

SELECT SUM(quantity) FROM components;

1	SELECT SUM(quantity) FROM components;

mysql> SELECT SUM(quantity) FROM components;
+---------------+
| SUM(quantity) |
+---------------+
|           110 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT SUM(quantity) FROM components;

+---------------+

| SUM(quantity) |

+---------------+

| 110 |

+---------------+

1 row in set (0.00 sec)

インラインビューによる合計列の追加

インラインビューに別名を定義し、元のテーブルと併記することで、全レコードに新たな合計値の列が追加される。

SELECT component, quantity, total
FROM
  components,
  (SELECT SUM(quantity) AS total FROM components) AS view
;

SELECT component, quantity, total

FROM

components,

(SELECT SUM(quantity) AS total FROM components) AS view

;

mysql> SELECT component, quantity, total
    -> FROM
    ->   components,
    ->   (SELECT SUM(quantity) AS total FROM components) AS view
    -> ;
+-----------+----------+-------+
| component | quantity | total |
+-----------+----------+-------+
| 成分A     |       60 |   110 |
| 成分B     |       35 |   110 |
| 成分C     |       15 |   110 |
+-----------+----------+-------+
3 rows in set (0.00 sec)

mysql> SELECT component, quantity, total

-> FROM

-> components,

-> (SELECT SUM(quantity) AS total FROM components) AS view

-> ;

+-----------+----------+-------+

| component | quantity | total |

+-----------+----------+-------+

| 成分A | 60 | 110 |

| 成分B | 35 | 110 |

| 成分C | 15 | 110 |

+-----------+----------+-------+

3 rows in set (0.00 sec)

各レコードのシェアの計算

各レコードのquantityを合計値で割ってシェアを計算する。

SELECT
  component,
  quantity,
  total,
  quantity / total AS rate
FROM
  components,
  (SELECT SUM(quantity) AS total FROM components) AS view
;

SELECT

component,

quantity,

total,

quantity / total AS rate

FROM

components,

(SELECT SUM(quantity) AS total FROM components) AS view

;

mysql> SELECT
    ->   component,
    ->   quantity,
    ->   total,
    ->   quantity / total AS rate
    -> FROM
    ->   components,
    ->   (SELECT SUM(quantity) AS total FROM components) AS view
    -> ;
+-----------+----------+-------+--------+
| component | quantity | total | rate   |
+-----------+----------+-------+--------+
| 成分A     |       60 |   110 | 0.5455 |
| 成分B     |       35 |   110 | 0.3182 |
| 成分C     |       15 |   110 | 0.1364 |
+-----------+----------+-------+--------+
3 rows in set (0.00 sec)

mysql> SELECT

-> component,

-> quantity,

-> total,

-> quantity / total AS rate

-> FROM

-> components,

-> (SELECT SUM(quantity) AS total FROM components) AS view

-> ;

+-----------+----------+-------+--------+

+-----------+----------+-------+--------+

| 成分A | 60 | 110 | 0.5455 |

| 成分B | 35 | 110 | 0.3182 |

| 成分C | 15 | 110 | 0.1364 |

+-----------+----------+-------+--------+

3 rows in set (0.00 sec)