Category: MySQL

まず、テーブルを作ります。

mysql> create table staff(
-> id int,
-> name varchar(10),
-> deptid int
-> );
Query OK, 0 rows affected (0.20 sec)

mysql> insert into staff values(1, ‘yamada’, 1);
Query OK, 1 row affected (0.11 sec)

mysql> insert into staff values(2, ‘honda’, 4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into staff values(3, ‘kudou’, 6);
Query OK, 1 row affected (0.03 sec)

mysql> insert into staff values(4, ‘nishi’, 1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into staff values(5, ‘tagawa’, 3);
Query OK, 1 row affected (0.06 sec)

mysql> select * from staff;
+——+——–+——–+
| id | name | deptid |
+——+——–+——–+
| 1 | yamada | 1 |
| 2 | honda | 4 |
| 3 | kudou | 6 |
| 4 | nishi | 1 |
| 5 | tagawa | 3 |
+——+——–+——–+
5 rows in set (0.00 sec)

更にもう一つテーブルを作ります。
mysql> create table dept(id int, name varchar(10));
Query OK, 0 rows affected (0.15 sec)

mysql> insert into dept values(1, ‘Develop’);
Query OK, 1 row affected (0.07 sec)

mysql> insert into dept values(3, ‘Legal’);
Query OK, 1 row affected (0.01 sec)

mysql> insert into dept values(5, ‘Planning’);
Query OK, 1 row affected (0.02 sec)

mysql> insert into dept values(6, ‘Marketing’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from dept;
+——+———–+
| id | name |
+——+———–+
| 1 | Develop |
| 3 | Legal |
| 5 | Planning |
| 6 | Marketing |
+——+———–+
4 rows in set (0.00 sec)

さーそれではleft.joinを使ってみましょう。

mysql> select * from staff left join dept on staff.deptid = dept.id;
+——+——–+——–+——+———–+
| id | name | deptid | id | name |
+——+——–+——–+——+———–+
| 1 | yamada | 1 | 1 | Develop |
| 4 | nishi | 1 | 1 | Develop |
| 5 | tagawa | 3 | 3 | Legal |
| 3 | kudou | 6 | 6 | Marketing |
| 2 | honda | 4 | NULL | NULL |
+——+——–+——–+——+———–+
5 rows in set (0.05 sec)

おおおおおおおおおおおおお。逆バージョン

mysql> select * from dept left join staff on dept.id = staff.deptid;
+——+———–+——+——–+——–+
| id | name | id | name | deptid |
+——+———–+——+——–+——–+
| 1 | Develop | 1 | yamada | 1 |
| 6 | Marketing | 3 | kudou | 6 |
| 1 | Develop | 4 | nishi | 1 |
| 3 | Legal | 5 | tagawa | 3 |
| 5 | Planning | NULL | NULL | NULL |
+——+———–+——+——–+——–+
5 rows in set (0.00 sec)

並び順がidではないだね。んーん。

カラムを指定する場合は、
mysql> select * from staff.id, staff.name, dept.name left join dept on staff.deptid = dept.id;
ERROR 1146 (42S02): Table ‘staff.id’ doesn’t exist

あれ、ちゃう。こうやな。
select staff.id, staff.name, dept.name from staff left join dept on staff.deptid = dept.id;

まずテーブルを作ります。
mysql> create database user;
Query OK, 1 row affected (0.09 sec)

mysql> use user;
Database changed

mysql> create table `user`(
-> `id` int(11) not null auto_increment,
-> `name` varchar(255) not null,
-> `email` varchar(255) not null,
-> `password` varchar(255) not null,
-> `created` datetime not null,
-> `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> `deleted` datetime DEFAULT null,
-> PRIMARY KEY(`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
-> ;
Query OK, 0 rows affected (0.48 sec)

# ストアドプロシージャ
DELIMITER //
CREATE PROCEDURE testInsert(IN max INT)
    BEGIN
    DECLARE cnt INT Default 1;
        simple_loop: LOOP
            INSERT INTO user(name, email, password, created) VALUES (CONCAT('user', cnt), concat('test', cnt, '@test.com'), concat('test_password', cnt), NOW());
            SET cnt = cnt+1;
            If cnt=max THEN
                LEAVE simple_loop;
            END IF;
        END LOOP simple_loop;
    END //

   ## インサート
   CALL testInsert(10000000);

   ## ストアドプロシージャーの削除
   DROP PROCEDURE testInsert;

ストアドプロシージャーって何？

selectでwhereを使うときに早くなるのはわかるのだが、仕組みがよくわからない。
add indexでindexを追加する、ということで

mysql> describe items;
+———+———+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+———+———+——+—–+———+—————-+
| item_id | int(11) | NO | PRI | NULL | auto_increment |
| name | text | YES | | NULL | |
| price | int(11) | YES | | NULL | |
+———+———+——+—–+———+—————-+
3 rows in set (0.00 sec)

mysql> alter table items add index idx_hoge(name(5));
Query OK, 0 rows affected (0.49 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe items;
+———+———+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+———+———+——+—–+———+—————-+
| item_id | int(11) | NO | PRI | NULL | auto_increment |
| name | text | YES | MUL | NULL | |
| price | int(11) | YES | | NULL | |
+———+———+——+—–+———+—————-+
3 rows in set (0.00 sec)

mysql> explain select * from items;
+—-+————-+——-+——+—————+——+———+——+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——+——-+
| 1 | SIMPLE | items | ALL | NULL | NULL | NULL | NULL | 3 | NULL |
+—-+————-+——-+——+—————+——+———+——+——+——-+
1 row in set (0.00 sec)

あれ？
>INDEXを作成すると、データテーブルとは別に検索用に最適化された状態でデータが保存
データ型そのものを変えるとは違う？　どういうことだ？

＞基本的にはデータ構造に対してというより
＞そのテーブルに走るSQLに対応してINDEXは設定します。
データ構造は変わらない。つまり、indexを張った後も、
mysql> describe items;
+———+———+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+———+———+——+—–+———+—————-+
| item_id | int(11) | NO | PRI | NULL | auto_increment |
| name | text | YES | MUL | NULL | |
| price | int(11) | YES | | NULL | |
+———+———+——+—–+———+—————-+
3 rows in set (0.00 sec)

name のkeyがmulになっているが。。

mysql> explain select name from hogehoge where hoge > 20000;
+—-+————-+———-+——+—————+——+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———-+——+—————+——+———+——+——+————-+
| 1 | SIMPLE | hoge | ALL | NULL | NULL | NULL | NULL | 67 | Using where |
+—-+————-+———-+——+—————+——+———+——+——+————-+
1 row in set (0.00 sec)

んん、なんか全然意味が分からない。
とりえあずindexはカラムに対して貼るってことはわかった。

What is an EXPLAIN statement?
EXPLAIN is a statement for obtaining information about execution plan of SQL. The execution plan is the result of MySQL’s decision as to which index (or table scan without index) will process the query.

mysql> select * from items;
+———+———+——-+
| item_id | name | price |
+———+———+——-+
| 1 | U+1F363 | NULL |
+———+———+——-+
1 row in set (0.06 sec)

mysql> explain select * from items;
+—-+————-+——-+——+—————+——+———+——+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——+——-+
| 1 | SIMPLE | items | ALL | NULL | NULL | NULL | NULL | 3 | NULL |
+—-+————-+——-+——+—————+——+———+——+——+——-+
1 row in set (0.00 sec)

select_typeはクエリの種類を表すものであり、ズバリツリーの構造にそのまま反映される。
クエリの種類とはJOIN、サブクエリ、UNIONおよびそれらの組み合わせ。

idは実行順番を表す

The TIME type is a data type used to store time. The format in which ‘HH:MM:SS’ is the basis when inputting time as a string.

here is a sample.

mysql> create table timetest(
    -> id int auto_increment primary key,
    -> starttime time,
    -> endtime time
    -> )
    -> ;
Query OK, 0 rows affected (0.17 sec)

mysql> select * from timetest;
Empty set (0.00 sec)

mysql> describe timetest;
+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) | NO   | PRI | NULL    | auto_increment |
| starttime | time    | YES  |     | NULL    |                |
| endtime   | time    | YES  |     | NULL    |                |
+-----------+---------+------+-----+---------+----------------+
3 rows in set (0.07 sec)

mysql> insert into timetest(starttime, endtime) values('08:00', '12:00');
Query OK, 1 row affected (0.07 sec)

mysql> select * from timtetest;
ERROR 1146 (42S02): Table 'test.timtetest' doesn't exist
mysql> select * from timetest;
+----+-----------+----------+
| id | starttime | endtime  |
+----+-----------+----------+
|  1 | 08:00:00  | 12:00:00 |
+----+-----------+----------+
1 row in set (0.00 sec)

I see.

From the PHP point of view, MySQL’s datetime type is just a string, so it is OK if you convert the current time to MySQL’s datetime type format using current time with date() and class DateTime.

Here is a solution.

$stmt->bindParam(':created_at',date('Y-m-d H:i:s'), PDO::PARAM_STR);

Connect and write

$last_name = $_POST['last_name'];
$first_name = $_POST['first_name'];
$flast_name = $_POST['flast_name'];
$ffirst_name = $_POST['ffirst_name'];
$birth = $_POST['birth'];
$mail = $_POST['mail'];
$tel = $_POST['tel'];
$job_place = $_POST['job-place'];
$salary = $_POST['salary'];
$filename = $_POST['filename'];
$upload = $_POST['upload'];

try {
	$pdo = new PDO('mysql:host=localhost;dbname=engineer;charset=utf8','root','',array(PDO::ATTR_EMULATE_PREPARES => false));
} catch (PDOException $e){
	exit('データベース接続失敗'. $e->getMessage());
}
$stmt = $pdo -> prepare("INSERT INTO register(last_name, first_name, flast_name, ffirst_name, birth, mail, tel, job_place, salary, filename, upload, created_at) values(:last_name, :first_name, :flast_name, :ffirst_name, :birth, :mail, :tel, :job_place, :salary, :filename, :upload, :created_at)");
$stmt->bindParam(':last_name',$last_name, PDO::PARAM_STR);
$stmt->bindParam(':first_name',$first_name, PDO::PARAM_STR);
$stmt->bindParam(':flast_name',$flast_name, PDO::PARAM_STR);
$stmt->bindParam(':ffirst_name',$ffirst_name, PDO::PARAM_STR);
$stmt->bindParam(':birth',$birth, PDO::PARAM_STR);
$stmt->bindParam(':mail',$mail, PDO::PARAM_STR);
$stmt->bindParam(':tel',$tel, PDO::PARAM_STR);
$stmt->bindParam(':job_place',$job_place, PDO::PARAM_STR);
$stmt->bindParam(':salary',$salary, PDO::PARAM_INT);
$stmt->bindParam(':filename',$filename, PDO::PARAM_STR);
$stmt->bindParam(':upload',$upload, PDO::PARAM_STR);
$stmt->bindParam(':created_at',date('Y-m-d H:i:s'), PDO::PARAM_STR);
$stmt->execute();

mysql> select * from register;
+----+-----------+------------+------------+-------------+-------+--------------      ------+---------------+-----------+--------+---------------+--------------------      -----------------+---------------------+
| id | last_name | first_name | flast_name | ffirst_name | birth | mail                     | tel           | job_place | salary | filename      | upload                                    | created_at          |
+----+-----------+------------+------------+-------------+-------+--------------      ------+---------------+-----------+--------+---------------+--------------------      -----------------+---------------------+
|  1 | 田中      | 太郎       | タナカ     | タロウ      |  1985 | engineer@gmai      l.com | 080-1234-5678 | 関東      |      0 |               | ./cfile/20190519071      727              | 2019-05-19 07:17:29 |
|  2 | 田中      | 太郎       | タナカ     | タロウ      |  1985 | engineer@gmai      l.com | 080-1234-5678 | 関東      |      0 | nightsky.jpeg | ./cfile/20190519071      835nightsky.jpeg | 2019-05-19 07:18:37 |
+----+-----------+------------+------------+-------------+-------+--------------      ------+---------------+-----------+--------+---------------+--------------------      -----------------+---------------------+
2 rows in set (0.00 sec)

Also want to send a email.

flush privileges
-> 権限の反映？
GRANT, REVOKE, or SET PASSWORDなどのステートメントでは必要ない？？

手動で権限テーブルをリロードするには、FLUSH PRIVILEGESステートメントを発行？
ふーん、そうなのか。。

グローバルレベル
GRANT ALL ON *.* TO user;
GRANT SELECT, INSERT ON *.* TO user;

データベースレベル
GRANT ALL ON db_name.* TO user;
GRANT SELECT, INSERT ON mydb.* TO user;

テーブルレベル
GRANT ALL ON db_name.table_name TO user;
GRANT SELECT, INSERT ON db_name.mytbl TO user;

カラムレベル
GRANT SELECT (col1), INSERT (col1, col2) ON db_name.table_name TO user;

実際にやってみましょう。
mysql> create user saru@localhost identified by ‘monkey’;
Query OK, 0 rows affected (0.59 sec)

mysql> show grants for saru@localhost;
+————————————————————————————————————-+
| Grants for saru@localhost |
+————————————————————————————————————-+
| GRANT USAGE ON *.* TO ‘saru’@’localhost’ IDENTIFIED BY PASSWORD ‘*A5892368AE83685440A1E27D012306B073BDF5B7’ |
+————————————————————————————————————-+
1 row in set (0.09 sec)

mysql> grant create on *.* to saru@localhost;
Query OK, 0 rows affected (0.05 sec)

mysql> show grants for saru@localhost;
+————————————————————————————————————–+
| Grants for saru@localhost |
+————————————————————————————————————–+
| GRANT CREATE ON *.* TO ‘saru’@’localhost’ IDENTIFIED BY PASSWORD ‘*A5892368AE83685440A1E27D012306B073BDF5B7’ |
+————————————————————————————————————–+
1 row in set (0.00 sec)

なるほど、grant userの後に、grantで権限を付けるわけですね。
ちょっと感動しました。

It is a command to view the list of currently running porcesses.
Used it to confirm that the batch is moving, or when didn’t get back a heavy query.

まず、show processlistとします。

mysql> show processlist
-> ;
+—-+——+———–+——+———+——+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+———–+——+———+——+——-+——————+
| 2 | root | localhost | demo | Query | 0 | init | show processlist |
+—-+——+———–+——+———+——+——-+——————+
1 row in set (0.21 sec)

続いて、別のユーザでログインしてみます。
mysql> show processlist;
+—-+——+———–+——+———+——+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+———–+——+———+——+——-+——————+
| 2 | root | localhost | demo | Query | 1 | init | show processlist |
| 3 | root | localhost | test | Sleep | 13 | | NULL |
+—-+——+———–+——+———+——+——-+——————+
2 rows in set (1.67 sec)

なるほど、状態が分かりますね。素晴らしい。

ユーザー権限の確認方法

show grants for [username]@[servername];

mysql> show grants for ‘root’@’localhost’;
+———————————————————————+
| Grants for root@localhost |
+———————————————————————+
| GRANT ALL PRIVILEGES ON *.* TO ‘root’@’localhost’ WITH GRANT OPTION |
| GRANT PROXY ON ”@” TO ‘root’@’localhost’ WITH GRANT OPTION |
+———————————————————————+
2 rows in set (0.05 sec)

mysql> show grants
-> ;
+———————————————————————+
| Grants for root@localhost |
+———————————————————————+
| GRANT ALL PRIVILEGES ON *.* TO ‘root’@’localhost’ WITH GRANT OPTION |
| GRANT PROXY ON ”@” TO ‘root’@’localhost’ WITH GRANT OPTION |
+———————————————————————+
2 rows in set (0.00 sec)

なるほどねー