20200915のMySQLに関する記事は3件です。

投稿日：2020-09-15T20:36:52+09:00

【GCP】MySQLのリードレプリカ（Master-Slave)構成の構築。

前提

GCPのCompute Engine(Ubuntu)にMySQLをインストールした。
MySQL 8.0

登場するインスタンス

web1 10.146.0.8
web2 10.146.0.9
mysql1 10.146.0.18
mysql2 10.146.0.20

手順

Step 1: Configure the Master Server

$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

/etc/mysql/mysql.conf.d/mysqld.cnfには下記の値が記載されるようにします。

/etc/mysql/mysql.conf.d/mysqld.cnf

log_bin = /var/log/mysql/mysql-bin.log
log_bin_index =/var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index

server-id = 1
tmpdir = /tmp
binlog_format = ROW
max_binlog_size = 500M
sync_binlog = 1
expire-logs-days = 7
slow_query_log

Step 2: Create Replication user on Replica database server

レプリカサーバーからリードサーバーに接続できるようにするためユーザーを作成します。

rootユーザーとして下記を実行します。

create user rpl_user@10.146.0.20 identified by 'password';

# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.21-0ubuntu0.20.04.4 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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.

mysql> create user rpl_user@10.146.0.20 identified with mysql_native_password by 'password';
Query OK, 0 rows affected (0.01 sec)

caching_sha2_passwordプラグインではなくmysql_native_passwordプラグインを指定しています。

mysql> grant replication slave on *.* to rpl_user@10.146.0.20;
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for rpl_user@10.146.0.20;
+------------------------------------------------------------+
| Grants for rpl_user@10.146.0.20                            |
+------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `rpl_user`@`10.146.0.20` |
+------------------------------------------------------------+
1 row in set (0.00 sec)

Step 3: Install and Configure Slave Server

$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

/etc/mysql/mysql.conf.d/mysqld.cnfには下記の値が記載されるようにします。

/etc/mysql/mysql.conf.d/mysqld.cnf

log_bin = /var/log/mysql/mysql-bin.log
log_bin_index =/var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index

server-id = 2
read_only = 1
tmpdir = /tmp
binlog_format = ROW
max_binlog_size = 500M
sync_binlog = 1
expire-logs-days = 7
slow_query_log   = 1

$ sudo systemctl restart mysql

Step 4: Initialize Replication process

レプリカサーバーの方で下記のmysqlを実行します。

mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 156
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

レプリカサーバーで下記のクエリを実行します。

CHANGE MASTER TO MASTER_HOST='10.146.0.18',
MASTER_USER='rpl_user',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=156;

作業ログ

# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.21-0ubuntu0.20.04.4 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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.

mysql>

mysql> CHANGE MASTER TO MASTER_HOST='10.146.0.18',
    -> MASTER_USER='rpl_user',
    -> MASTER_PASSWORD='password',
    -> MASTER_LOG_FILE='mysql-bin.000002',
    -> MASTER_LOG_POS=156;
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

レプリカのステータスを確認します。

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.146.0.18
                  Master_User: rpl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 156
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 371
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 156
              Relay_Log_Space: 580
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 1cbeabec-f0cc-11ea-b096-42010a920012
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set (0.00 sec)

step 5. Check

replication_dbをリードDBで作成し、レプリカDBで確認をしてみる。

root@mysql-1:/home/kuraya# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.21-0ubuntu0.20.04.4 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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.

mysql>
mysql> CREATE DATABASE replication_db;
Query OK, 1 row affected (0.01 sec)

root@mysql-2:/home/kuraya# sudo mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.21-0ubuntu0.20.04.4 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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.

mysql> SHOW DATABASES;
+----------------------+
| Database             |
+----------------------+
| information_schema   |
| mysql                |
| performance_schema   |
| replication_db       |
| sys                  |
| to*****bo_production |
+----------------------+
6 rows in set (0.01 sec)

リードで作成したDBであるreplication_dbがレプリカでも確認することができました。

（このreplication_dbはリードDBで削除しておきます。）

参考：
https://www.tecmint.com/setup-mysql-master-slave-replication-on-ubuntu/

　 Tweet
Qiitaで続きを読む

投稿日：2020-09-15T12:16:54+09:00

【CakePHP】MySQLに接続のエラー「SQLSTATE[HY000] [2002] No such file or directory」の対処法

対策法

一つ目はこれ。
https://qiita.com/Yorinton/items/bfdf962fe4b7339866f6

一つ目がダメだったら、app/config/app.phpでdatasousrceのusername、password、database、hostがあっているかどうかをチェックする。

　 Tweet
Qiitaで続きを読む

投稿日：2020-09-15T10:03:59+09:00

MySQL メモランダム

ドットインストールの学習メモです

MySQLの始め方

ローカル開発環境で

[vagrant@localhost MySQL]$ mysql -u root

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

データベースの操作

現在のユーザー表示,データベース一覧表示

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.01 sec)

データベース作成、

mysql> create database mydb01;
Query OK, 1 row affected (0.00 sec)

mysql> create database mydb02;
Query OK, 1 row affected (0.00 sec)

mysql> create database mydb03;
Query OK, 1 row affected (0.00 sec)

mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb01             |
| mydb02             |
| mydb03             |
| mysql              |
| performance_schema |
+--------------------+
6 rows in set (0.00 sec)

データベース削除

mysql> drop database mydb03;
Query OK, 0 rows affected (0.01 sec)

mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb01             |
| mydb02             |
| mysql              |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)

現在のデータベース表示・変更

mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql> use mydb02;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| mydb02     |
+------------+
1 row in set (0.00 sec)

大文字小文字の区別はない


mysql> SelEct DAtabase();
+------------+
| DAtabase() |
+------------+
| mydb02     |
+------------+
1 row in set (0.00 sec)

mysql>

作業用ユーザーの設定

ルートユーザーで実行
create user dbuser01@localhost identified by 'password';
- dbuser01というユーザーを作り、パスワードを設定。
grant all on mydb01.* to dbuser01@localhost;
- dbuser01にmydb01の全テーブルに関するすべての権限を与える。

root userから dbuser01へ
- パスワードとデータベースを指定してログイン。

mysql> \q
Bye
[vagrant@localhost MySQL]$ mysql -u dbuser01 -p mydb01;
Enter password:

ユーザーの変更と、データベースの指定を確認(mydb01のみ）

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb01             |
+--------------------+
2 rows in set (0.00 sec)

ユーザーの削除
drop user dbuser@localhost;

外部ファイルのコマンドの実行

ファイルを作成

drop database if exists myapp;
create database myapp;
grant all on myapp.* to myapp_user@localhost identified by 'matasaburou09';

ファイル名を指定して実行

[vagrant@localhost MySQL]$ mysql -u root < create_myapp.sql;
[vagrant@localhost MySQL]$ mysql -u myapp_user -p myapp;

rootユーザーでログインしてから、ファイルを指定して実行

mysql> source ./create_myapp.sql

もしくは
mysql> \. ./create_myapp.sql

テーブルの操作

sql

drop table if exists users;
create table users(
    id int unsigned,
    name varchar(20),
    score float
);

（テーブルの削除）
テーブルの作成
- id, name, score　のフィールドの作成

terminal

mysql> \. ./myapp.sql
Query OK, 0 rows affected (0.01 sec)

mysql> show tables
    -> ;
+-----------------+
| Tables_in_myapp |
+-----------------+
| users           |
+-----------------+
1 row in set (0.00 sec)

mysql> desc users;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | YES  |     | NULL    |       |
| name  | varchar(20)      | YES  |     | NULL    |       |
| score | float            | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> drop table users;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

desc って　description　の略なんだって。初めて聞いた・・・。

データ型について

number
- int 整数
- float 小数
- double floatより精度が高い
- int unsigned +の値しか使わない
string
- char(4) 固定長のデータ 4けた
- varchar(255)　255バイトまでの可変長文字列
- text どれくらいの長さかわからない場合
date/time
- date
- time
- datetime '2020-08-09 12:34:22'
true/false:
- boolean -> tinyint(1)と同じ意味一桁の整数の方の別名
- true -> 1 ↓以外
- false -> 0 null

データ型

テーブルデータの挿入

-- insert into users (id, name, score) values(1, 'tagichi', 5.8);
-- insert into users (id, name, score) values(2, 'fkoji', 8.2);
-- insert into users (id, name, score) values(3, 'dotinstall', 6.1);
-- insert into users (id, name, score) values(4, 'yamada', null);
insert into users (id, name, score) values
(1, 'tagichi', 5.8),
(2, 'fkoji', 8.2),
(3, 'dotinstall', 6.1),
(4, 'yamada', null);

select * from users;

フィールドに制限をかけてみる

drop table if exists users;
create table users(
    id int unsigned primary key auto_increment,
    name varchar(20) unique,
    -- score float not null
    score float default 0.0
);
desc users;

primary key 主キー
auto_icrement 連番割り振り
unipue 重複を許さず

-- insert into users (id, name, score) values
-- (1, 'tagichi', 5.8),
-- (2, 'fkoji', 8.2),
-- (3, 'dotinstall', 6.1),
-- (4, 'yamada', null);
-- (5, 'tanaka');
-- (5, 'tanaka');
insert into users (name, score) values
('tagichi', 5.8),
('fkoji', 8.2),
('dotinstall', 6.1),
('yamada', null);
-- ('tanaka');
-- ('tanaka');

select * from users;

ここから更新された新しいMySQL講座

日時・真偽値を使ってみよう

DROP TABLE IF EXISTS posts;
CREATE TABLE posts (
  message VARCHAR(140), 
  likes INT,
  is_draft BOOL,
  created DATETIME
);

INSERT INTO posts (message, likes,is_draft, created) VALUES 
  ('Tnanks', 12, TRUE, '2020-10-11 13:45:03'),
  ('Arigato', 4, FALSE, '2020-08-22'),
  ('Merci', 4, 0, NOW());

SELECT * FROM posts;

~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+---------+-------+----------+---------------------+
| message | likes | is_draft | created             |
+---------+-------+----------+---------------------+
| Tnanks  |    12 |        1 | 2020-10-11 13:45:03 |
| Arigato |     4 |        0 | 2020-08-22 00:00:00 |
| Merci   |     4 |        0 | 2020-09-11 15:40:30 |
+---------+-------+----------+---------------------+
~ $

SELECTを使ってデータを抽出

-- SELECT * FROM posts;
-- SELECT id, message FROM posts;
-- SELECT * FROM posts WHERE likes >= 10;

SELECT * FROM posts WHERE message = 'Danke';
SELECT * FROM posts WHERE message != 'Danke';
SELECT * FROM posts WHERE message <> 'Danke';

*　全て
FROM　テーブル
WHERE　条件

~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+----+---------+-------+
| id | message | likes |
+----+---------+-------+
|  5 | Danke   |    23 |
+----+---------+-------+
+----+---------+-------+
| id | message | likes |
+----+---------+-------+
|  1 | Thanks  |    12 |
|  2 | Arigato |     4 |
|  3 | Merci   |     4 |
|  4 | Gracias |    15 |
+----+---------+-------+
+----+---------+-------+
| id | message | likes |
+----+---------+-------+
|  1 | Thanks  |    12 |
|  2 | Arigato |     4 |
|  3 | Merci   |     4 |
|  4 | Gracias |    15 |
+----+---------+-------+
~ $

条件の組み合わせ

10以上２０以下
10位上２０以下
10より下、20より上

-- AND 尚且つ
-- OR もしくは
SELECT * FROM posts WHERE likes >= 10 AND likes <=20;
SELECT * FROM posts WHERE likes BETWEEN 10 AND 20;
SELECT * FROM posts WHERE likes NOT BETWEEN 10 AND 20;

~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+----+---------+-------+
| id | message | likes |
+----+---------+-------+
|  1 | Thanks  |    12 |
|  4 | Gracias |    15 |
+----+---------+-------+
+----+---------+-------+
| id | message | likes |
+----+---------+-------+
|  1 | Thanks  |    12 |
|  4 | Gracias |    15 |
+----+---------+-------+
+----+---------+-------+
| id | message | likes |
+----+---------+-------+
|  2 | Arigato |     4 |
|  3 | Merci   |     4 |
|  5 | Danke   |    23 |
+----+---------+-------+

likesが4か12
likesが4か12
上記以外

SELECT * FROM posts WHERE likes = 4 OR likes = 12;
SELECT * FROM posts WHERE likes IN (4,12);
SELECT * FROM posts WHERE likes NOT IN (4,12);

+----+---------+-------+
| id | message | likes |
+----+---------+-------+
|  1 | Thanks  |    12 |
|  2 | Arigato |     4 |
|  3 | Merci   |     4 |
+----+---------+-------+
+----+---------+-------+
| id | message | likes |
+----+---------+-------+
|  1 | Thanks  |    12 |
|  2 | Arigato |     4 |
|  3 | Merci   |     4 |
+----+---------+-------+
+----+---------+-------+
| id | message | likes |
+----+---------+-------+
|  4 | Gracias |    15 |
|  5 | Danke   |    23 |
+----+---------+-------+

LIKEと％で文字列を抽出しよう

SELECT * FROM posts WHERE message = 'Gracias';
% 0文字以上の任意の文字
_: 任意の一文字

-- SELECT * FROM posts WHERE message LIKE 't%';
SELECT * FROM posts WHERE message LIKE BINARY 't%';
SELECT * FROM posts WHERE message LIKE '%su';
SELECT * FROM posts WHERE message LIKE '%i%';

~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+----+-------------+-------+
| id | message     | likes |
+----+-------------+-------+
|  2 | thanks 100% |     4 |
+----+-------------+-------+
+----+-------------------+-------+
| id | message           | likes |
+----+-------------------+-------+
|  4 | Arigato_gozaimasu |    15 |
|  5 | Arigato! desu     |    23 |
+----+-------------------+-------+
+----+-------------------+-------+
| id | message           | likes |
+----+-------------------+-------+
|  3 | Gracias           |     4 |
|  4 | Arigato_gozaimasu |    15 |
|  5 | Arigato! desu     |    23 |
+----+-------------------+-------+

LIKEと％で文字列を抽出しよう

%: 0文字以上の任意の文字
_: 任意の1文字

SELECT * FROM posts WHERE message LIKE '__a%';
-- 3文字目がaの任意の文字
SELECT * FROM posts WHERE message NOT LIKE '__a%';
--それ以外の文字

SELECT * FROM posts WHERE message LIKE '%\%%';
-- ％の入ってる文字
SELECT * FROM posts WHERE message LIKE '%\_%';
-- _の入ってる文字

NULLのレコードを抽出

SELECT * FROM posts WHERE likes != 12;
  -- nullは含まれない
SELECT * FROM posts WHERE likes != 12 OR likes IS NULL;
-- nullも含まれた
SELECT * FROM posts WHERE likes IS NOT NULL;
-- null以外

nullが入ってる時は条件に注意が必要

抽出結果の並び替え

SELECT * FROM posts ORDER BY likes;
-- 小さい順
SELECT * FROM posts ORDER BY likes DESC;
-- 大きい順
SELECT * FROM posts ORDER BY likes DESC, message;
-- 大きい順でアルファベット順
SELECT * FROM posts ORDER BY likes DESC, message LIMIT 3;
-- 上記で上位3件のみ表示

SELECT * FROM posts ORDER BY likes DESC, message LIMIT 3 OFFSET 2;
-- 2件目まで除外してから3件分表示
SELECT * FROM posts ORDER BY likes DESC, message LIMIT 2, 3;
-- 同上

数値の関数を見ていこう

-- + - * / %

SELECT likes * 500 / 3 FROM posts;
-- 1likes で500円分、全体の3分の１をもらえる計算
SELECT likes * 500 / 3 AS bonus FROM posts;
-- ASで名称をつける
SELECT 
  likes * 500 / 3 AS bonus,
  FLOOR(likes * 500 /3) AS floor, -- 端数切り捨て
  CEIL(likes * 500 /3) AS ceil, -- 端数切り上げ
  ROUND(likes * 500 /3) AS round, -- 四捨五入
  ROUND(likes * 500 /3, 2) AS round -- 桁数の指定、小数点以下の桁
FROM 
  posts;
-- ()がついたものを関数という ex) round関数


+-----------+-------+------+-------+---------+
| bonus     | floor | ceil | round | round   |
+-----------+-------+------+-------+---------+
| 2000.0000 |  2000 | 2000 |  2000 | 2000.00 |
|  666.6667 |   666 |  667 |   667 |  666.67 |
|  666.6667 |   666 |  667 |   667 |  666.67 |
| 2500.0000 |  2500 | 2500 |  2500 | 2500.00 |
| 1333.3333 |  1333 | 1334 |  1333 | 1333.33 |
+-----------+-------+------+-------+---------+

文字列の関数〜文字列抽出、文字列の連結、文字列の長さ（日本語対応）〜

SELECT message, SUBSTRING(message, 3) FROM posts;
-- 3文字目以降
SELECT message, SUBSTRING(message, 3, 2) FROM posts;
-- 3文字目以降2文字分
SELECT message, SUBSTRING(message, -2) FROM posts;
-- 末尾から2文字分

SELECT CONCAT(message, ' _ ', likes) FROM posts;
-- 文字列の連結。メッセージといいねの数をハイフンでつなぐ

SELECT message, LENGTH(message) AS len FROM posts;
-- メッセージと文字の長さ。しかしありがとうは15文字扱い。
SELECT message, CHAR_LENGTH(message) AS len FROM posts;
-- 日本語にも対応 LENGTHのみ問題になる SUBSTRINGは問題ない。

~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+-----------------+-----------------------+
| message         | SUBSTRING(message, 3) |
+-----------------+-----------------------+
| Thanks          | anks                  |
| Merci           | rci                   |
| Arigato         | igato                 |
| ありがとう      | がとう                |
| Gracias         | acias                 |
| Danke           | nke                   |
+-----------------+-----------------------+
+-----------------+--------------------------+
| message         | SUBSTRING(message, 3, 2) |
+-----------------+--------------------------+
| Thanks          | an                       |
| Merci           | rc                       |
| Arigato         | ig                       |
| ありがとう      | がと                     |
| Gracias         | ac                       |
| Danke           | nk                       |
+-----------------+--------------------------+
+-----------------+------------------------+
| message         | SUBSTRING(message, -2) |
+-----------------+------------------------+
| Thanks          | ks                     |
| Merci           | ci                     |
| Arigato         | to                     |
| ありがとう      | とう                   |
| Gracias         | as                     |
| Danke           | ke                     |
+-----------------+------------------------+
+-------------------------------+
| CONCAT(message, ' _ ', likes) |
+-------------------------------+
| Thanks _ 12                   |
| Merci _ 4                     |
| Arigato _ 4                   |
| ありがとう _ 4                |
| Gracias _ 15                  |
| Danke _ 8                     |
+-------------------------------+
+-----------------+------+
| message         | len  |
+-----------------+------+
| Thanks          |    6 |
| Merci           |    5 |
| Arigato         |    7 |
| ありがとう      |   15 |
| Gracias         |    7 |
| Danke           |    5 |
+-----------------+------+
+-----------------+------+
| message         | len  |
+-----------------+------+
| Thanks          |    6 |
| Merci           |    5 |
| Arigato         |    7 |
| ありがとう      |    5 |
| Gracias         |    7 |
| Danke           |    5 |
+-----------------+------+

日時の関数　〜 DATE_FORMAT, INTERVAL, NOW() ~

SELECT created, YEAR(created) FROM posts;
SELECT created, MONTH(created) FROM posts;
SELECT created, DAY(created) FROM posts;
-- 年、月、日を抽出

SELECT 
   created,
   DATE_FORMAT(created, '%M %D %Y, %W') AS date
FROM
  posts;
-- わかりやすく表示

SELECT 
   created,

   DATE_ADD(created, INTERVAL 7 DAY) AS next
FROM
  posts;
-- 一週間後を表示

SELECT 
   created,
   NOW(),
   DATEDIFF(created, NOW()) AS diff
FROM
  posts;
-- 現在日時との差を表す

~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+---------------------+---------------+
| created             | YEAR(created) |
+---------------------+---------------+
| 2020-05-01 00:00:00 |          2020 |
| 2020-05-03 00:00:00 |          2020 |
| 2020-06-14 00:00:00 |          2020 |
| 2020-07-04 00:00:00 |          2020 |
| 2020-08-22 00:00:00 |          2020 |
+---------------------+---------------+
+---------------------+----------------+
| created             | MONTH(created) |
+---------------------+----------------+
| 2020-05-01 00:00:00 |              5 |
| 2020-05-03 00:00:00 |              5 |
| 2020-06-14 00:00:00 |              6 |
| 2020-07-04 00:00:00 |              7 |
| 2020-08-22 00:00:00 |              8 |
+---------------------+----------------+
+---------------------+--------------+
| created             | DAY(created) |
+---------------------+--------------+
| 2020-05-01 00:00:00 |            1 |
| 2020-05-03 00:00:00 |            3 |
| 2020-06-14 00:00:00 |           14 |
| 2020-07-04 00:00:00 |            4 |
| 2020-08-22 00:00:00 |           22 |
+---------------------+--------------+
+---------------------+----------------------------+
| created             | date                       |
+---------------------+----------------------------+
| 2020-05-01 00:00:00 | May 1st 2020, Friday       |
| 2020-05-03 00:00:00 | May 3rd 2020, Sunday       |
| 2020-06-14 00:00:00 | June 14th 2020, Sunday     |
| 2020-07-04 00:00:00 | July 4th 2020, Saturday    |
| 2020-08-22 00:00:00 | August 22nd 2020, Saturday |
+---------------------+----------------------------+
+---------------------+---------------------+
| created             | next                |
+---------------------+---------------------+
| 2020-05-01 00:00:00 | 2020-05-08 00:00:00 |
| 2020-05-03 00:00:00 | 2020-05-10 00:00:00 |
| 2020-06-14 00:00:00 | 2020-06-21 00:00:00 |
| 2020-07-04 00:00:00 | 2020-07-11 00:00:00 |
| 2020-08-22 00:00:00 | 2020-08-29 00:00:00 |
+---------------------+---------------------+
+---------------------+---------------------+------+
| created             | NOW()               | next |
+---------------------+---------------------+------+
| 2020-05-01 00:00:00 | 2020-09-15 08:45:03 | -137 |
| 2020-05-03 00:00:00 | 2020-09-15 08:45:03 | -135 |
| 2020-06-14 00:00:00 | 2020-09-15 08:45:03 |  -93 |
| 2020-07-04 00:00:00 | 2020-09-15 08:45:03 |  -73 |
| 2020-08-22 00:00:00 | 2020-09-15 08:45:03 |  -24 |
+---------------------+---------------------+------+
~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+---------------------+---------------+
| created             | YEAR(created) |
+---------------------+---------------+
| 2020-05-01 00:00:00 |          2020 |
| 2020-05-03 00:00:00 |          2020 |
| 2020-06-14 00:00:00 |          2020 |
| 2020-07-04 00:00:00 |          2020 |
| 2020-08-22 00:00:00 |          2020 |
+---------------------+---------------+
+---------------------+----------------+
| created             | MONTH(created) |
+---------------------+----------------+
| 2020-05-01 00:00:00 |              5 |
| 2020-05-03 00:00:00 |              5 |
| 2020-06-14 00:00:00 |              6 |
| 2020-07-04 00:00:00 |              7 |
| 2020-08-22 00:00:00 |              8 |
+---------------------+----------------+
+---------------------+--------------+
| created             | DAY(created) |
+---------------------+--------------+
| 2020-05-01 00:00:00 |            1 |
| 2020-05-03 00:00:00 |            3 |
| 2020-06-14 00:00:00 |           14 |
| 2020-07-04 00:00:00 |            4 |
| 2020-08-22 00:00:00 |           22 |
+---------------------+--------------+
+---------------------+----------------------------+
| created             | date                       |
+---------------------+----------------------------+
| 2020-05-01 00:00:00 | May 1st 2020, Friday       |
| 2020-05-03 00:00:00 | May 3rd 2020, Sunday       |
| 2020-06-14 00:00:00 | June 14th 2020, Sunday     |
| 2020-07-04 00:00:00 | July 4th 2020, Saturday    |
| 2020-08-22 00:00:00 | August 22nd 2020, Saturday |
+---------------------+----------------------------+
+---------------------+---------------------+
| created             | next                |
+---------------------+---------------------+
| 2020-05-01 00:00:00 | 2020-05-08 00:00:00 |
| 2020-05-03 00:00:00 | 2020-05-10 00:00:00 |
| 2020-06-14 00:00:00 | 2020-06-21 00:00:00 |
| 2020-07-04 00:00:00 | 2020-07-11 00:00:00 |
| 2020-08-22 00:00:00 | 2020-08-29 00:00:00 |
+---------------------+---------------------+
+---------------------+---------------------+------+
| created             | NOW()               | diff |
+---------------------+---------------------+------+
| 2020-05-01 00:00:00 | 2020-09-15 08:45:47 | -137 |
| 2020-05-03 00:00:00 | 2020-09-15 08:45:47 | -135 |
| 2020-06-14 00:00:00 | 2020-09-15 08:45:47 |  -93 |
| 2020-07-04 00:00:00 | 2020-09-15 08:45:47 |  -73 |
| 2020-08-22 00:00:00 | 2020-09-15 08:45:47 |  -24 |
+---------------------+---------------------+------+

レコードの更新〜　UPDATE　SET、　UPPER

-- SELECT likes + 10 FROM posts;
-- UPDATE posts SET likes = likes + 5 WHERE likes >= 10 ;
-- likes10以上にlikesを5プラスする
UPDATE 
  posts 
SET 
  likes = likes + 5,
  message = UPPER(message)
WHERE
  likes >= 10;
-- 上記に加えて、更新したものを大文字にする。
SELECT * FROM posts;


~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+----+---------+-------+
| id | message | likes |
+----+---------+-------+
|  1 | Thanks  |    17 |
|  2 | Merci   |     4 |
|  3 | Arigato |     4 |
|  4 | Gracias |    20 |
|  5 | Danke   |     8 |
+----+---------+-------+
~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+----+---------+-------+
| id | message | likes |
+----+---------+-------+
|  1 | THANKS  |    17 |
|  2 | Merci   |     4 |
|  3 | Arigato |     4 |
|  4 | GRACIAS |    20 |
|  5 | Danke   |     8 |
+----+---------+-------+

レコードの削除 ~ DELETE FROM, TRUNCATE TABLE ~

-- DELETE FROM posts WHERE likes <= 10;
-- likesが１０以下のものを削除
TRUNCATE TABLE posts;
-- 連番ごと削除できる。

INSERT INTO posts (message, likes) VALUES('Xie Xie', 10);
-- 連番は一度使われたものは使われない。

SELECT * FROM posts;

DELETE テーブル名　WHERE　条件
IDの連番は削除できないので、 TRUNCATE TABLE テーブル名　を使う。

作成・更新日時を自動で設定　~ created, updated DATETIME DEFAULT NOW(), UPDATE~ SET , ~

DROP TABLE IF EXISTS posts;
CREATE TABLE posts (
  id INT NOT NULL AUTO_INCREMENT,
  message VARCHAR(140), 
  likes INT,
  created DATETIME DEFAULT NOW(),
  -- フィールドを追加。レコードが挿入された時点をNOW()で記録。
  updated DATETIME DEFAULT NOW() ON UPDATE NOW(),
  -- フィールドを追加。更新された時点での日時も追加 ON UPDATE NOW()。
  PRIMARY KEY (id)
);

INSERT INTO posts (message, likes) VALUES 
  ('Thanks', 12),
  ('Merci', 4),
  ('Arigato', 4),
  ('Gracias', 15),
  ('Danke', 8);

SELECT id, created, updated FROM posts;
-- データを表示
SELECT SLEEP(3);
-- 3秒待つ
UPDATE posts SET likes = 100 WHERE id = 1;
-- id が1のlikesを100にする。
SELECT id, created, updated FROM posts;
-- もう一度表示。
SELECT * FROM posts;

~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+----+---------------------+---------------------+
| id | created             | updated             |
+----+---------------------+---------------------+
|  1 | 2020-09-15 09:42:52 | 2020-09-15 09:42:52 |
|  2 | 2020-09-15 09:42:52 | 2020-09-15 09:42:52 |
|  3 | 2020-09-15 09:42:52 | 2020-09-15 09:42:52 |
|  4 | 2020-09-15 09:42:52 | 2020-09-15 09:42:52 |
|  5 | 2020-09-15 09:42:52 | 2020-09-15 09:42:52 |
+----+---------------------+---------------------+
+----------+
| SLEEP(3) |
+----------+
|        0 |
+----------+
+----+---------------------+---------------------+
| id | created             | updated             |
+----+---------------------+---------------------+
|  1 | 2020-09-15 09:42:52 | 2020-09-15 09:42:56 |
|  2 | 2020-09-15 09:42:52 | 2020-09-15 09:42:52 |
|  3 | 2020-09-15 09:42:52 | 2020-09-15 09:42:52 |
|  4 | 2020-09-15 09:42:52 | 2020-09-15 09:42:52 |
|  5 | 2020-09-15 09:42:52 | 2020-09-15 09:42:52 |
+----+---------------------+---------------------+
+----+---------+-------+---------------------+---------------------+
| id | message | likes | created             | updated             |
+----+---------+-------+---------------------+---------------------+
|  1 | Thanks  |   100 | 2020-09-15 09:42:52 | 2020-09-15 09:42:56 |
|  2 | Merci   |     4 | 2020-09-15 09:42:52 | 2020-09-15 09:42:52 |
|  3 | Arigato |     4 | 2020-09-15 09:42:52 | 2020-09-15 09:42:52 |
|  4 | Gracias |    15 | 2020-09-15 09:42:52 | 2020-09-15 09:42:52 |
|  5 | Danke   |     8 | 2020-09-15 09:42:52 | 2020-09-15 09:42:52 |
+----+---------+-------+---------------------+---------------------+

テーブルの設計の変更 ~ ALTER TABLE , ADD (AFTER,FIRST), CHANGE, RENAME ~

- ALTER TABLE posts ADD author VARCHAR (255);
-- ALTER TABLE posts ADD author VARCHAR (255) AFTER id;
-- idの後にauthor挿入
ALTER TABLE posts ADD author VARCHAR (255) FIRST;
-- BEFOREはない！最初に入れるFIRSTだけ！  

ALTER TABLE posts DROP message;
-- メッセージの削除
ALTER TABLE posts CHANGE likes points INT;
-- フィールド名をlikes を pointsに変更。 changeは前のデータが消えることもあるので注意！
DROP TABLE IF EXISTS messages;
-- messagesがすでにあったら削除
ALTER TABLE posts RENAME messages;
-- テーブル名をpostsからmessagesに変更！
SHOW TABLES;

-- DESC posts;


+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| author  | varchar(255) | YES  |     | NULL    |                |
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| message | varchar(140) | YES  |     | NULL    |                |
| likes   | int(11)      | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| author | varchar(255) | YES  |     | NULL    |                |
| id     | int(11)      | NO   | PRI | NULL    | auto_increment |
| likes  | int(11)      | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+
~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+-----------------+
| Tables_in_myapp |
+-----------------+
| messages        |
+-----------------+

何やらなんやらで時間がかかってしまったので最初の部分忘れてる・・・。
まあ先に進もう。

　 Tweet
Qiitaで続きを読む

20200915のMySQLに関する記事は3件です。

【GCP】MySQLのリードレプリカ（Master-Slave)構成の構築。

登場するインスタンス

手順

Step 1: Configure the Master Server

Step 2: Create Replication user on Replica database server

Step 3: Install and Configure Slave Server

Step 4: Initialize Replication process

step 5. Check

【CakePHP】MySQLに接続のエラー「SQLSTATE[HY000] [2002] No such file or directory」の対処法

対策法

MySQL メモランダム

MySQLの始め方

データベースの操作

作業用ユーザーの設定

外部ファイルのコマンドの実行

テーブルの操作

データ型について

テーブルデータの挿入

フィールドに制限をかけてみる

日時・真偽値を使ってみよう

SELECTを使ってデータを抽出

条件の組み合わせ

LIKEと％で文字列を抽出しよう

LIKEと％で文字列を抽出しよう

NULLのレコードを抽出

抽出結果の並び替え

数値の関数を見ていこう

文字列の関数〜文字列抽出、文字列の連結、文字列の長さ（日本語対応）〜

日時の関数　〜 DATE_FORMAT, INTERVAL, NOW() ~

レコードの更新〜　UPDATE　SET、　UPPER

レコードの削除 ~ DELETE FROM, TRUNCATE TABLE ~

作成・更新日時を自動で設定　~ created, updated DATETIME DEFAULT NOW(), UPDATE~ SET , ~

テーブルの設計の変更 ~ ALTER TABLE , ADD (AFTER,FIRST), CHANGE, RENAME ~

日付別

タグ一覧

20200915のMySQLに関する記事は3件です。

【GCP】MySQLのリードレプリカ（Master-Slave)構成の構築。

登場するインスタンス

手順

Step 1: Configure the Master Server

Step 2: Create Replication user on Replica database server

Step 3: Install and Configure Slave Server

Step 4: Initialize Replication process

step 5. Check

【CakePHP】MySQLに接続のエラー「SQLSTATE[HY000] [2002] No such file or directory」の対処法

対策法

MySQL メモランダム

MySQLの始め方

データベースの操作

作業用ユーザーの設定

外部ファイルのコマンドの実行

テーブルの操作

データ型について

テーブルデータの挿入

フィールドに制限をかけてみる

日時・真偽値を使ってみよう

SELECTを使ってデータを抽出

条件の組み合わせ

LIKEと％で文字列を抽出しよう

LIKEと％で文字列を抽出しよう

NULLのレコードを抽出

抽出結果の並び替え

数値の関数を見ていこう

文字列の関数 〜文字列抽出、文字列の連結、文字列の長さ（日本語対応）〜

日時の関数 〜 DATE_FORMAT, INTERVAL, NOW() ~

レコードの更新 〜 UPDATE SET、 UPPER

レコードの削除 ~ DELETE FROM, TRUNCATE TABLE ~

作成・更新日時を自動で設定 ~ created, updated DATETIME DEFAULT NOW(), UPDATE~ SET , ~

テーブルの設計の変更 ~ ALTER TABLE , ADD (AFTER,FIRST), CHANGE, RENAME ~

日付別

タグ一覧

文字列の関数〜文字列抽出、文字列の連結、文字列の長さ（日本語対応）〜

日時の関数　〜 DATE_FORMAT, INTERVAL, NOW() ~

レコードの更新〜　UPDATE　SET、　UPPER

作成・更新日時を自動で設定　~ created, updated DATETIME DEFAULT NOW(), UPDATE~ SET , ~