SQLパターンマッチングで比較

SQLパターンマッチングで比較


select * from test_tbl where name like binary "Koba%";

binaryで大文字、小文字を区別します。

通常は、区別なく比較します。

エスケープ処理

ワイルドカード文字列を'%'や'_'という文字としてマッチさせたい場合

¥%

¥_

ワイルドカード

%   任意の0文字以上の文字列

_   任意の1文字

MySQLでCSVファイルがうまくいかなかった場合

MySQLでCSVファイルがうまくいかなかった場合

(ubuntudeで、mysqlバージョンは、５．７)


ｍｙｓｑｌからファイル出力（CSVファイルなど）を出力しようとしたら
エラーになった場合は

以下のフォルダ名を追加して、そこに出力しましょう！

'/var/lib/mysql-files/test.txt'

そのフォルダのパーミションを変更して、だれでも読み書きできるようにしました。
（実行権限はいらないね）




エラーメッセージ
mysql> select 1 into outfile 'test.txt';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

正解
mysql> select "testdata"  into outfile '/var/lib/mysql-files/test.txt';
Query OK, 1 row affected (0.00 sec)

パーミションの変更
ll /var/lib/

drwxr-xr-x  2 root          root          4096 12月 17 07:35 mlocate/
drwxr-xr-x  2 root          root          4096  7月  8 17:55 msttcorefonts/
drwx------ 10 mysql         mysql         4096 12月 17 08:57 mysql/
drwxrwxrwx  2 mysql         mysql         4096 12月 17 08:37 mysql-files/
drwx------  2 mysql         mysql         4096  5月 14  2017 mysql-keyring/
drwxr-xr-x  2 root          root          4096  4月 27  2017 mysql-upgrade/
drwxr-xr-x  2 root          root          4096  5月 13  2017 nssdb/
drwxr-xr-x  2 root          root          4096  3月  7  2012 ntpdate/

別のやり方

secure_file_privの値を""にすると、どこでも読めるらしい。




sudo vi /etc/mysql/mysql.conf.d/mysqld.cnfを編集します。
ファイルの最後に以下を追加する。（もしあれば変更）

secure-file-priv=""

以下をmysql内で確認するとできた。

しかし、/tmp以外のフォルダではNG

❍　select "testdata"  into outfile '/tmp/test.txt';

✗　select "testdata"  into outfile '/home/koba/test/test.txt';

そこで、

secure-file-priv="/home/koba/test/"

でどうだ〜

mysql> select "testdata"  into outfile '/tmp/test2.txt';

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

mysql> select "testdata"  into outfile '/home/koba/test/test2.txt';

ERROR 1 (HY000): Can't create/write to file '/home/koba/test/test2.txt' (Errcode: 13 - Permission denied)

全然だめ、パーミションは変更したのに！

色々やっていたが、ある時うまくいった。（原因？）

・apparmorの設定をおこなっていた（）
・再起動した

・chown で所有者、グループを変更していた

sudo gedit /etc/apparmor.d/usr.sbin.mysqld
sudo /etc/init.d/apparmor reload

sudo /etc/init.d/apparmor restarｔ

まあー、現在時点では、わかったのは。。。

（mysqlからCSVファイルなどを出力したい）

①/etc/mysql/mysql.conf.d/mysqld.cnfを編集

　「secure-file-priv=""」を追加した

②フォルダを自分のユーザーの適当なフォルダを作成

　パーミションは、所有者、グループが（読み、書き、実行）でその他が実行権

　（何も考えないでフォルダ作成しただけ）

③そのフォルダを、chownでグループだけを変更

　（グループをmysqlにしないと、mysqldがうまく書けない）

❍ubuntudeで、mysqlバージョンは、５．７
mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.7.18-0ubuntu0.16.04.1 |
+-------------------------+

❍確認するらしい
mysql> select @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| /var/lib/mysql-files/     |
+---------------------------+
1 row in set (0.00 sec)



❍フォルダ

 /var/lib/mysql-files

❍再起動

sudo /etc/init.d/mysql restart

mysql データーベースのコピーをつくる

データベースの複数を作成する

mysqldump データーベース名 > dump.sql
mysqladmin create コピー先のデーターベース
mysql  コピー先のデーターベース < dump.sql

テーブルデータの取得

指定したデータベース内のすべてのテーブルの構造と中身を出力する。

テーブル名.sql　テーブル名の構造

テーブル名.txt 　タブ区切りでテーブル内容が格納

mysqldump --tab=格納先フォルダ名 データベース名

（例）

mysqldump --tab=/home/koba/csv psoft_db

mysql ubuntu 16.4 character set 文字化け 漢字

mysqlで漢字コードをutf8にする

●mysqlのバージョンを確認する

mysql -u root -p

mysql> select version();

+-------------------------+
| version()               |
+-------------------------+
| 5.7.18-0ubuntu0.16.04.1 |
+-------------------------+
1 row in set (0.00 sec)


●my.cnfの編集

sudo vi /etc/mysql/my.cnf

[mysqld]

character-set-server=utf8

[mysql]

#default-character-set=latin1

#    

#  utf8にする

default-character-set=utf8

●mysqlを再起動する

sudo /etc/init.d/mysql restart

[ ok ] Restarting mysql (via systemctl): mysql.service.

●文字コードの確認

mysql> 

mysql> status

--------------

mysql  Ver 14.14 Distrib 5.7.18, for Linux (i686) using  EditLine wrapper

Connection id: 4

Current database:

Current user: test_user@localhost

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server version: 5.7.18-0ubuntu0.16.04.1 (Ubuntu)

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: utf8

Db     characterset: utf8

Client characterset: utf8

Conn.  characterset: utf8

UNIX socket: /var/run/mysqld/mysqld.sock

Uptime: 1 min 11 sec

Threads: 1  Questions: 6  Slow queries: 0  Opens: 107  Flush tables: 1  Open tables: 26  Queries per second avg: 0.084

--------------

mysql> 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/share/mysql/charsets/ |

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

ケース１（サーバー側が latin１）

my.cnfの内容と漢字コードの関係を確認してみました

my.cnfの内容

!includedir /etc/mysql/conf.d/

!includedir /etc/mysql/mysql.conf.d/

[mysqld]

character-set-server=latin1

[mysql]

default-character-set=utf8

漢字コード確認(mysql)

mysql> status

--------------

mysql  Ver 14.14 Distrib 5.7.18, for Linux (i686) using  EditLine wrapper

Connection id: 5

Current database:

Current user: test_user@localhost

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server version: 5.7.18-0ubuntu0.16.04.1 (Ubuntu)

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: latin1

Db     characterset: latin1

Client characterset: utf8

Conn.  characterset: utf8

UNIX socket: /var/run/mysqld/mysqld.sock

Uptime: 25 sec

Threads: 1  Questions: 6  Slow queries: 0  Opens: 107  Flush tables: 1  Open tables: 26  Queries per second avg: 0.240

--------------

mysql> 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     | latin1                     |

| character_set_system     | utf8                       |

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

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

ケース２(おそらくデフォルト、クライアントがlatin1になっている)

my.cnfの内容

[mysqld]

character-set-server=utf8

[mysql]

default-character-set=latin1

漢字コード確認(mysql)

mysql> status

--------------

mysql  Ver 14.14 Distrib 5.7.18, for Linux (i686) using  EditLine wrapper

Connection id: 4

Current database:

Current user: test_user@localhost

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server version: 5.7.18-0ubuntu0.16.04.1 (Ubuntu)

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: utf8

Db     characterset: utf8

Client characterset: latin1

Conn.  characterset: latin1

UNIX socket: /var/run/mysqld/mysqld.sock

Uptime: 52 sec

Threads: 1  Questions: 6  Slow queries: 0  Opens: 107  Flush tables: 1  Open tables: 26  Queries per second avg: 0.115

--------------

mysql> show variables like 'char%';

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

| Variable_name            | Value                      |

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

| character_set_client     | latin1                     |

| character_set_connection | latin1                     |

| character_set_database   | utf8                       |

| character_set_filesystem | binary                     |

| character_set_results    | latin1                     |

| character_set_server     | utf8                       |

| character_set_system     | utf8                       |

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

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

ケース３（これでOK）

my.cnf

[mysqld]

character-set-server=utf8

[mysql]

default-character-set=utf8

漢字コード確認(mysql)

mysql> status

--------------

mysql  Ver 14.14 Distrib 5.7.18, for Linux (i686) using  EditLine wrapper

Connection id: 4

Current database:

Current user: test_user@localhost

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server version: 5.7.18-0ubuntu0.16.04.1 (Ubuntu)

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: utf8

Db     characterset: utf8

Client characterset: utf8

Conn.  characterset: utf8

UNIX socket: /var/run/mysqld/mysqld.sock

Uptime: 1 min 11 sec

Threads: 1  Questions: 6  Slow queries: 0  Opens: 107  Flush tables: 1  Open tables: 26  Queries per second avg: 0.084

--------------

mysql> 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/share/mysql/charsets/ |

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

MySQLコマンド行ツール

ライターのシンプル・ケンケン棒です。

本日は、コマンドラインでmysqlを触ってみたいと思います。




まず最初に起動方法は、以下です。
ユーザーは、ルート(root)で、パスワードありです。

mysql -u root -p
（パスワードは-pの後は、スペースがなし）


・ユーザーの作成
・作成したユーザーに権限をつける
・データベースの作成


・ユーザーの作成
　create user test_user identified by 'pwd123';
　select user,host from mysql.user;

・グローバルレベルで付与する
　grant all on *.* to test_user;
　show grants for test_user;

・データーベース作成

　create databases test_db;

データーベース作成で、漢字コード指定の場合は、

　create databases test_db default character set utf8; 

権限をデータベースレベルで付与したい場合は、
　grant all on test_db.* to test_user;




ここからは、本ちゃんです。

• テーブル作成,削除
• データの追加、変更、削除、検索

次にやることは、設計かな、

名前を決めます。

　ユーザー名

　データベース名

ユーザー名は、test_user
データベース名は、test_user

テーブルの設計

今回は、名前と住所だけです。

＊ポイントは、テーブには行と列があります。

追加、変更、削除、検索は。。。

INSERT INTO テーブル名 VALUES(値...)

UPDATE テーブル名 SET name='???' WHERE id=5

DELETE FROM テーブル名 WHERE id=5

SELECT * FROM テーブル名 WHERE id　>=5

これでOKですね。

実際には、こんな感じです。

INSERT INTO test_table(id, money)  VALUES( 55, 105.5);

DELETE FROM test_table WHERE id=55;

UPDATE test_table SET name='山田太朗' where id=54;

SELECT * FROM test_table;





その他

show databases;

show tables;

show character set;

show collation;

ユーザーの作成

create user test_user identified by 'pwd123';
select user,host from mysql.user;

グローバルレベルで付与する
grant all on *.* to test_user;

データベースレベルで付与する
grant all on test_db.* to test_user;

show grants for test_user;

データーベース作成

create databases test_db default character set utf8; 

drop table test_table;

create table test_table(
 id  int(10),
 name  char(50),
 money dec(10,2)
);

show columns from test_table;

●命名規則
　結構自分は、あまり気にはしませんがうるさい人は、うるさいです。

　ハイフンはマイナス間違えるので使用しない
　では、アンダースコアーですね
　※データーベース名にハイフン使用は引用符でくくらないとエラーみたいです

　小文字でなるべく省略しない事。
　自分は、古いPGなので。。。どうかなーでも賛成ですよ

　まとめた例
　　test_db
       test_tblでなくてtest_table（テーブルは単数）


●文字コードの確認
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/share/mysql/charsets/ |
+--------------------------+----------------------------+