mysqlでストアドプロシージャを勉強中。MS sql serverとはまた違う

平成30年1月13日
mysqlでストアドプロシージャを勉強中。MS sql serverとはまた違う

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| masedaDB |
| test2017 |
+——————–+
3 rows in set (0.00 sec)

前提テーブルは4つのカラムを持つ。このloginメルアド列のメルアドを修正します。
MYSQLでストアドプロシージャを使います。SOURCE ファイル名で読み込むことができるらしいですが、今はエラーが出まくりでできませんでしたので、コンソールで登録しました。

mysql> select * from users;
+—-+—————+————+———————+
| id | login | company_id | create_at |
+—-+—————+————+———————+
| 1 | ma@jp.ooo.com | 1 | 2018-01-13 11:41:34 |
+—-+—————+————+———————+

文字列に別の文字をつけるストアドプロシージャ

ある文字列を削除する用な感じで、特定の文字列を含まないように文字位置から最後の文字までを取得
old_aaa@gmail.comの場合aaa@gmail.com
となるようにold_は4文字なので5文字目からメルアドの文字を取得する

mysql> use test2017
Database changed
mysql> show tables;
+——————–+
| Tables_in_test2017 |
+——————–+
| area |
| perchase |
| product |
| user |
+——————–+
4 rows in set (0.02 sec)
以下は一行ずつ入力してリターンして、終わったらセミコロンを入力しました。
テスト用のテーブルを作成
mysql> create table users(
-> id int not null primary key,
-> login nvarchar(100),
-> company_id int,
-> create_at datetime
-> );
Query OK, 0 rows affected (0.04 sec)

mysql> select * from users;
Empty set (0.01 sec)

ダミーのデータを入力
mysql> insert into users(id,login,company_id,create_at)
-> select 1,’ma@jp.ooo.com’,1,now();
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from users;
+—-+—————+————+———————+
| id | login | company_id | create_at |
+—-+—————+————+———————+
| 1 | ma@jp.ooo.com | 1 | 2018-01-13 11:41:34 |
+—-+—————+————+———————+
1 row in set (0.00 sec)

mysql> insert into users(id,login,company_id,create_at)
-> select 2,’mi@gmail.com’,1,now();
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from users; +—-+—————+————+———————+
| id | login | company_id | create_at |
+—-+—————+————+———————+
| 1 | ma@jp.ooo.com | 1 | 2018-01-13 11:41:34 |
| 2 | mi@gmail.com | 1 | 2018-01-13 11:43:41 |
+—-+—————+————+———————+
2 rows in set (0.00 sec)

ストアドプロシージャをSourceで読み込むと
エラーばかりでわからん。
mysql> source simpleproc_zacbin.sql;
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 3
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 ‘DECLARE myCur CURSOR FOR SELECT * FROM users WHERE company_id = 1’ at line 1
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 ‘OPEN myCur’ at line 1
Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

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 ‘WHILE @myCounter > @pos DO
FETCH myCur INTO myLogin’ at line 1
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 ‘–myNo,myArea,myPref’ at line 1
Query OK, 0 rows affected (0.00 sec)

ERROR 1054 (42S22): Unknown column ‘myLogin’ in ‘field list’
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 ‘–count myLogin chars
SET @cutMail = substring(myLogin,5,@strLength)’ at line 1
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 ‘–cut org_
UPDATE users’ at line 1
ERROR 1193 (HY000): Unknown system variable ‘login’
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 ‘WHERE company_id = 1’ at line 1
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 ‘–IF myLogin =’愛知県’ THEN
–SET param2 = ‘指定範囲に愛知県を’ at line 1
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 ‘–END IF’ at line 1
Query OK, 0 rows affected (0.01 sec)

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 ‘END WHILE’ at line 1
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 ‘CLOSE myCur’ at line 1
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 ‘END’ at line 1
このエラーの回避策がわからん!!!!!!!!!!!!!!!!!!!!!!!
たぶん、コンソールを入力する記述とSourceで呼び出す記述が違うんだと思うけど、よくわからん。
仕方ないので、コンソールで入力する

手順が大事★★
mysql> delimiter // (デリミターを変える)

コピペします
mysql> CREATE PROCEDURE simpleproc3()
-> BEGIN
-> DECLARE myLogin NVARCHAR(100);
-> DECLARE myCreateAt datetime;
-> DECLARE myCur CURSOR FOR SELECT login,create_at FROM users WHERE company_id = 1;
->
-> OPEN myCur;
-> SELECT COUNT(*) INTO @myCounter FROM users WHERE company_id = 1;
-> SET @pos = 0;
-> WHILE @myCounter > @pos DO
-> FETCH myCur INTO myLogin, myCreateAt;
-> select mylogin,myCreateAt;
-> SET @pos = @pos +1;
-> END WHILE;
-> CLOSE myCur;
-> END
->
-> // (このデリミターを忘れずに!!)
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;(デリミターを戻す)
mysql> call simpleproc3(); (実行する。EXECと同じ)
+———+———————+
| mylogin | myCreateAt |
+———+———————+
| NULL | 2018-01-13 11:41:34 |
+———+———————+
1 row in set (0.00 sec)

+———+———————+
| mylogin | myCreateAt |
+———+———————+
| NULL | 2018-01-13 11:43:41 |
+———+———————+
1 row in set (0.00 sec)
あれ??????うまく行っていない。

mylogin,myCreateAtはカラム名ではなくて、ストアドプロシージャで宣言した変数です。

次はメールアドレスの変更です。
単にメルアドをまとめてある文字列を付加します。
まずはテーブルデータのメールアドレスにold_をつける
そして、old_を消すストアドを作る。
いわゆる文字列変更です。

まず文字が存在するか位置はどこかを調べる
locateは文字がないと0を返す
mysql> select locate(‘c’,’1111′);
+——————–+
| locate(‘c’,’1111′) |
+——————–+
| 0 |
+——————–+
1 row in set (0.00 sec)
3が1文字目に発見される
mysql> select locate(‘3′,’311112’);
+———————-+
| locate(‘3′,’311112′) |
+———————-+
| 1 |
+———————-+
1 row in set (0.00 sec)

mysql> select locate(’31’,’311112′);
+———————–+
| locate(’31’,’311112′) |
+———————–+
| 1 |
+———————–+
1 row in set (0.00 sec)

メールアドレスに、old_が付いていない場合だけ、付けたい
mysql> select * from users;
+—-+——————+————+———————+
| id | login | company_id | create_at |
+—-+——————+————+———————+
| 1 | ma@jp.ooo.com | 1 | 2018-01-13 11:41:34 |
| 2 | old_ka@gmail.com | 1 | 2018-01-13 11:43:41 |
+—-+——————+————+———————+
2 rows in set (0.00 sec)

mysql> call changemail();
Query OK, 0 rows affected (0.00 sec)

先程のma@にだけold_が付加されました。

mysql> select * from users;
+—-+——————-+————+———————+
| id | login | company_id | create_at |
+—-+——————-+————+———————+
| 1 | old_ma@jp.ooo.com | 1 | 2018-01-13 11:41:34 |
| 2 | old_ka@gmail.com | 1 | 2018-01-13 11:43:41 |
+—-+——————-+————+———————+
2 rows in set (0.00 sec)
プロシージャの削除
mysql> drop procedure changemail;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from users;
+—-+——————+————+———————+
| id | login | company_id | create_at |
+—-+——————+————+———————+
| 1 | ma@jp.oro.com | 1 | 2018-01-13 11:41:34 |
| 2 | old_ka@gmail.com | 1 | 2018-01-13 11:43:41 |
+—-+——————+————+———————+
2 rows in set (0.00 sec)

mysql> call changemail();
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users;
+—-+——————-+————+———————+
| id | login | company_id | create_at |
+—-+——————-+————+———————+
| 1 | old_ma@jp.oro.com | 1 | 2018-01-13 11:41:34 |
| 2 | old_ka@gmail.com | 1 | 2018-01-13 11:43:41 |
+—-+——————-+————+———————+
2 rows in set (0.00 sec)

mysql> drop procedure changemail;
:::::

mysql> delimiter //
(コピペした)
mysql> /* change email -> old_email
/*> */
mysql> CREATE PROCEDURE changemail()
-> BEGIN
-> DECLARE myID int;
-> DECLARE myLogin NVARCHAR(100);
-> DECLARE myCreateAt datetime;
-> DECLARE myCur CURSOR FOR SELECT id,login,create_at FROM users WHERE company_id = 1;
->
-> OPEN myCur;
-> SELECT COUNT(*) INTO @myCounter FROM users WHERE company_id = 1;
-> SET @pos = 0;
-> WHILE @myCounter > @pos DO
-> FETCH myCur INTO myID,myLogin, myCreateAt;
-> /*old_ not exist add old_ word
/*> email->old_email
/*> */
-> IF locate(‘old_’,myLogin) = 0 THEN /*search old_*/
-> update users set login = concat(‘old_’,myLogin) where id = myID and company_id = 1;
-> END IF;
-> SET @pos = @pos +1;
-> END WHILE;
-> CLOSE myCur;
->
->
-> END
-> //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> call changemail();
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users;
+—-+——————-+————+———————+
| id | login | company_id | create_at |
+—-+——————-+————+———————+
| 1 | old_ma@jp.oro.com | 1 | 2018-01-13 11:41:34 |
| 2 | old_ka@gmail.com | 1 | 2018-01-13 11:43:41 |
+—-+——————-+————+———————+
2 rows in set (0.00 sec)
(テスト用にold_をけしたメルアドを修正)
mysql> update users set login = ‘ma@jp.oro.com’ where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from users;
+—-+——————+————+———————+
| id | login | company_id | create_at |
+—-+——————+————+———————+
| 1 | ma@jp.oro.com | 1 | 2018-01-13 11:41:34 |
| 2 | old_ka@gmail.com | 1 | 2018-01-13 11:43:41 |
+—-+——————+————+———————+
2 rows in set (0.00 sec)

mysql> call changemail();
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users;
+—-+——————-+————+———————+
| id | login | company_id | create_at |
+—-+——————-+————+———————+
| 1 | old_ma@jp.oro.com | 1 | 2018-01-13 11:41:34 |
| 2 | old_ka@gmail.com | 1 | 2018-01-13 11:43:41 |
+—-+——————-+————+———————+
2 rows in set (0.00 sec)

mysql> drop procedure changemail;
Query OK, 0 rows affected (0.01 sec)

selectでテーブルをCSV出力でINTO OUTFILEが利用できないときは

-u ユーザ
-D DB名
-p パスワードの入力
>testcsv.csv ファイル名

aidayota-no-MacBook-Pro:mysql maseda$ /usr/local/mysql/bin/mysql -u maseda -D test2017 -p -e “select * from users where company_id = 1;” > testcsv.csv
Enter password: (パスワード)
aidayota-no-MacBook-Pro:mysql maseda$ ls
region.sql testcsv.csv>>>ここにできた。

aidayota-no-MacBook-Pro:mysql maseda$ cat testcsv.csv
id login company_id create_at
1 ma@jp.ooo.com 1 2018-01-13 11:41:34
2 ka@gmail.com 1 2018-01-13 11:43:41
5 na@jp.ooo.com 1 2018-01-14 11:29:35

こちらの記事もどうぞ