MySQL触ってて、他のコードの変更とか方針の変更に応じてテーブルのカラムを挿入したくなることってあるよね。(自分があった)ので、テーブルにカラムを挿入する方法を共有します。

MySQLで困った!カラム追加したい

<h2>MySQLにアクセス</h2>
最初に、MySQLにアクセスして対話モードを実行していきましょう。
<pre><code class="hljs language-bash">$~ mysql -u root -p #root権限で入ってみる
Enter password: **********
~~ 以下略 ~~

mysql> 
</code></pre>
まずはコレで準備OK
<h2>テーブルの型のチェックと期待する変更結果の提示</h2>
さて、ここからカラムを追加していきましょう。元々のテーブル(example_tableと名付ける)は以下を想定します。

















<table><thead><tr><th>user</th><th>age</th><th>height</th><th>weight</th></tr></thead><tbody><tr><td>VARCHAR(32)</td><td>INT</td><td>FLOAT</td><td>FLOAT</td></tr></tbody></table>
まずは調べてみましょう。
<pre><code class="hljs language-sql">mysql> DESCRIBE example_table;
+-----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| user | varchar(32) | YES | | NULL | |
| age | int | YES | | NULL | |
| height | float | YES | | NULL | |
| weight | float | YES | | NULL | |
+-----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
</code></pre>
そしてここにBMIを定義するカラムを追加してみたいと思います。想定する結果がこちらです。



















<table><thead><tr><th>user</th><th>age</th><th>height</th><th>weight</th><th>bmi</th></tr></thead><tbody><tr><td>VARCHAR(32)</td><td>INT</td><td>FLOAT</td><td>FLOAT</td><td>FLOAT</td></tr></tbody></table>
<h2>カラムを追加する</h2>
さて、先ほどの期待結果に合わせてカラムを挿入していきましょう。コマンドの構文としてはこちらになります。
ALTER TABLE テーブル名 ADD カラム名 型 (オプション);
それでは実際にコマンドを実行していきましょう。さきの追加によると、

















<table><thead><tr><th>テーブル名</th><th>カラム名</th><th>型</th><th>オプション</th></tr></thead><tbody><tr><td>example_table</td><td>bmi</td><td>FLOAT</td><td>なし</td></tr></tbody></table>
こちらの情報をもとに構文に当てはめます。
<pre><code class="hljs language-sql">mysql> ALTER TABLE example_table ADD bmi FLOAT;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
</code></pre>
さて、どう変わったのかみてみましょう。
<pre><code class="hljs language-sql">mysql> DESCRIBE example_table;
+-----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| user | varchar(32) | YES | | NULL | |
| age | int | YES | | NULL | |
| height | float | YES | | NULL | |
| weight | float | YES | | NULL | |
| bmi | float | YES | | NULL | |
+-----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
</code></pre>
うまくいったようですね。最後の行に期待した通りのカラムが追加されているようです。
<h2>まとめ</h2>
構文は以下になります。
ALTER TABLE テーブル名 ADD カラム名 型 (オプション);
カラムをいくつか増やしたいときは複数同時に指定してカラムを足すこともできます。
いつも複数データを扱っているときのように()で括って処理を書きます。
ALTER TABLE テーブル名 ADD (カラム名 型, カラム名 型, ...);
このような感じでカラムを追加することができます。必要に応じてNULLを使えなくしたりデフォルト値を決めたりするといいでしょう。
構文自体はこんな感じでした。技術的に必要になった知識がでたら、また公開していきます。

MySQLでリモート接続のためのセットアップを行いました。現環境はバージョン8.0.19なのですが、前回からのメジャーアップデートにて、コマンドが変わっており大変苦労したので備忘録として。かなりつまずきますので共有します。

MySQL8.0.xでリモート接続のセットアップをする!

現在conoHa VPSにMySQLの環境を作り外部からアクセスしようとしていました。しかし、ここで問題が...
リモート接続するためのセットアップってどうやるん？ 
しかも何や。記事まねても全然うまくいかないやん...orz
ってことなのですが、バージョン8にメジャーアップデートにあたり、コマンドの変更があったようなので共有します。
で、今回はroot権限をどこからでも接続する権限を与えます。
<h2>sshにてリモートサーバにアクセス</h2>
まずははじめに、コマンドラインでsshを使ってリモートサーバに入ります。今回はルートとして入ります。
<pre><code class="hljs language-bash">$~ ssh root@xxx.xxx.x.xxx #@以降はアドレス
Enter password: **********
~~ 以下略 ~~

[root@xxx-xxx-x-xxx]# 
</code></pre>
このようにログイン先の情報とともに表示されます。
<h2>【本題】権限を与える</h2>
まずはMySQLを起動しまして、以下のコマンドを打ちます。
<pre><code class="hljs language-sql">mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'your-password';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT all PRIVILEGES
 -> ON *.* TO 'root'@'%' 
 -> WITH GRANT OPTION;
</code></pre>
まずは1つ目のコマンドでユーザーを作ります。(コレが8.0.x以降の仕様なのでドツボにハマるやつ!!)
そして2つ目のコマンドにて、権限を付与してきます。ただ、今回は見易さのため複数行にしましたが、1行でも問題はないです。(大文字小文字もあまり気にしなくて良かったりもする)
あと、'%'ってんはアクセスするIPアドレスを書くところであって、今回はどこからでもアクセスできる(どのPCからでも)を想定するので**'%'**を指定して全てを許可します。
あと、***.***の部分はデータベースとテーブルの設定ですが、こちらも全てにアクセスができると想定してこちらにしています。任意のデータベースを許可したければ '任意の名前'.*とでもすれば良いでしょう。
一応コレで下準備は完了ってところですかね。
基本的には問題ないのですが、僕が使ってみたconoHa VPSとかだと、ファイヤウォールの設定が必要なので今回は省略。
ドツボにハマるのはきっとここなので注意。
<h2>実際にマイナーバージョンでのコマンド打ってみる</h2>
実際にマイナーバージョンでのコマンドを実行してみますか。(愚行) 
あと小文字でやってみた。
<pre><code class="hljs language-sql">mysql> grant all privileges on 
 -> *.* to root@"%" identified by 'your-password' 
 -> with grant option;
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 'identified by 'your-password'
with grant option' at line 2
</code></pre>
まあこうなってわからないよね。SQL構文チェックしようねって言われるわけだけど、記事も少なかったし、ここはドツボにハマりかけるのかなと思います。
<h2>まとめ</h2>
ルートとして外部からどこでもどのデータベースのアクセスを許可するには以下のコマンドを打ちましょう。
<pre><code class="hljs language-sql">mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'your-password';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT all PRIVILEGES
 -> ON *.* TO 'root'@'%' 
 -> WITH GRANT OPTION;
</code></pre>
他にも外部サーバの設定のよって動かないことがあるので、それはまた紹介しますが。
今回はバージョン8.0.xでの変更点に触れました。
快適なデータベースライフを!!

結構初歩的なのだけど、ユーザー権限を削除したくなって構文を覚えたので共有します。

MySQLでユーザーの権限を削除する

root権限で何でもかんでも行うのはすごく怖かったので、新たにユーザーを登録していました。しかし、登録したはいいものの削除したくもなるわけです。
そこで今回は登録されているユーザーを削除する流れを紹介していきます。
<h2>MySQLにアクセス</h2>
最初に、MySQLにアクセスして対話モードを実行していきましょう。
<pre><code class="hljs language-bash">$~ mysql -u root -p #root権限で入ってみる
Enter password: **********
~~ 以下略 ~~

mysql> 
</code></pre>
まずはコレで準備OK
<h2>とりあえずユーザー名とホストを調べる</h2>
ユーザー名とホスト名を調べましょう。ユーザー名が同じものがいくつかあることもありますので、同時にホストも調べておきます。 
消したいユーザーを念のためみておきます。
<pre><code class="hljs language-sql">mysql> SELECT user, host FROM mysql.user
+------------------+-----------+
| user | host |
+------------------+-----------+
| usr | localhost |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
</code></pre>
コレで確認ができましたね。下の4つは基本的に最初から入っているやつだと思います。
今回は、一番上のusrというユーザーのパーミッションを削除してやろうと思います。
<h2>ユーザーを削除する</h2>
実際に削除していきます。コマンドは以下のようになります。
<pre><code class="hljs language-sql">mysql> DROP USER usr@localhost;
</code></pre>
これでとりあえず削除は完了。上では、ユーザー名構文に則って削除しました。 
ユーザー名@ホストということですね。
<h2>ホストを与えないで削除してみる</h2>
先ほどは、ユーザー名@ホストの形式で削除しましたが、ホスト名を明記しなくても消すことはできます。
<pre><code class="hljs language-sql">mysql> DROP USER usr;
</code></pre>
こんな感じです。こうすると明記する場合でいう「usr@'%'」と同値になります。

Pythonのライブラリ「mysql-connector-python」を使っていたのですが、なかなか'SHOW TABLES'や'SHOW DATABASES'を出力できなかったので、その対処法を備忘録としてまとめます。

mysql-connector-pythonで'SHOW ~~~'に困ったのでその対処法

MySQLのデータベースを扱う際に、Pythonスクリプトから接続したくなったので、Pythonからの接続方法を紹介します。
<h2>mysql-connector-pythonのインストール</h2>
まずは、mysql-connector-pythonのインストールを行っていきます。(復習がてら)
今回は、pipを使ってインストールしていきます。
<pre><code class="hljs language-bash">$~ pip install mysql-connector-python
</code></pre>
これでエラーが出ずに進めばインストールできているはずです。とりあえずこれで準備は完了。
<h2>まずはconnectorをインポート</h2>
まずは接続するためのメソッドをインポートしないと使えないので、インポートしましょう。
<pre><code class="hljs language-python">import mysql.connector
</code></pre>
これで準備はOK!!
<h2>接続とDB操作のためのカーソルを生成、そして切断</h2>
今回の例では、以下の情報を前提にした上で、exampleというデータベースに直で接続していきます。

















<table><thead><tr><th>user</th><th>host</th><th>password</th><th>database</th></tr></thead><tbody><tr><td>root</td><td>127.0.0.1</td><td>example</td><td>example</td></tr></tbody></table>
さて、早速接続の処理を書いてみましょう。
<pre><code class="hljs language-python">cnx = mysql.connector.connect(
 user='root',
 password='example',
 database='example',
)
</code></pre>
これだけで接続は完了です。（パスワード等が全て正しければ） 
ホストはデフォルトが127.0.0.1なので書かなくていいです。
この後に、カーソルをあてます。
<pre><code class="hljs language-python">cur = cnx.cursor()
</code></pre>
この処理だけでオッケーです。
他の処理等も終わったら、しっかり切断しておきましょう。
<pre><code class="hljs language-python">cur.close()
cnx.close()
</code></pre>
これで接続と切断の処理は完了です。
<h2>SHOW TABLE表示していく</h2>
それでは、ここまでで準備は整ったので、*cur = cnx.cursor()*のすぐ後に以下の処理を書いてみましょう。
<pre><code class="hljs language-python">sql = "SHOW TABLES" #命令文
print(cur.execute(sql)) #結果の出力

# --> None #あれ？？ない
</code></pre>
見事失敗ですね(笑)
僕はずっと、実行した結果が戻り値として吐き出されていると思ってこの処理を書きました。しかし、それがないのでNoneが返ってきてしまうのですね...
それでは正しいスクリプトを書いていきましょう。
<pre><code class="hljs language-python">sql = "SHOW TABLES" #命令文
cur.execute(sql) #実行
print(cur.fetchone()) #1つだけフェッチする

# --> (example, )
</code></pre>
出力本体は人の環境によって異なりますが、このような感じでタプル型でテーブル名が出力されます。
複数処理結果をまとめて吐き出すには「fetchall()」というものがあるようです。今回は一つだけテーブルに対して情報を吐いてもらうので「fetchone()」で取得しました。
<h2>せっかくなので'SHOW DATABASES'もやってみる</h2>
せっかくなので「SHOW DATABASES」も出力してみましょう。DATABASE群をみるので、今回接続する際は先ほどのdatabase引数はなくします。















<table><thead><tr><th>user</th><th>host</th><th>password</th></tr></thead><tbody><tr><td>root</td><td>127.0.0.1</td><td>example</td></tr></tbody></table>
<pre><code class="hljs language-python">import mysql.connector

cnx = mysql.connector.connect(
 user='root',
 password='example',
 )

cur = cnx.cursor()

cur.execute("SHOW DATABASES")
print(cur.fetchall()) #&#x3C;&#x3C; fetchall()に注意

cur.close()
cnx.close()

# --> [(example, ), (sys, )]
</code></pre>
今回は複数のデータがあることに留意したとして、**fetchall()**しました。ここも見落としポイントが多い気がします。
僕はそうだったのですが、タプル型に全ての名前が入ると期待したのですが、実際は一つ一つの名前に対してタプルを作り、そのタプル群をリストに入れるようです。
<h2>まとめ</h2>
ここまでみてきた結果、一番安全なのはこれでしょうか。
<pre><code class="hljs language-python">cur = cnx.cursor()

cur.execute("SHOW DATABASES")
print(cur.fetchall()) 
</code></pre>
まずはカーソルを当ててから、そこでコマンドを実行する。そのあとは「fetchall()」でリストに包める。
情報が1つしかないことがわかっているならば問題ないのですが、基本的には複数あるという想定のもと、fetchall()で取得する方がいい気もしました。

Pythonのライブラリ「mysql-connector-python」を使って、ローカルのMySQLデータベースに接続します。

mysql-connector-pythonでローカルDBに接続する

MySQLのデータベースを扱う際に、Pythonスクリプトから接続したくなったので、Pythonからの接続方法を紹介します。
<h2>mysql-connector-pythonのインストール</h2>
まずは、mysql-connector-pythonのインストールを行っていきます。
今回は、pipを使ってインストールしていきます。
<pre><code class="hljs language-bash">$~ pip install mysql-connector-python
</code></pre>
これでエラーが出ずに進めばインストールできているはずです。とりあえずこれで準備は完了。
<h2>まずはconnectorをインポート</h2>
まずは接続するためのメソッドをインポートしないと使えないので、インポートしましょう。
<pre><code class="hljs language-python">import mysql.connector
</code></pre>
これで準備はOK!!
<h2>接続と切断</h2>
それではここまで完成できていれば、あとは接続の処理を書くだけです。
今回の例では、以下の情報を前提にしてみます。

















<table><thead><tr><th>user</th><th>host</th><th>password</th><th>database</th></tr></thead><tbody><tr><td>root</td><td>127.0.0.1</td><td>**********</td><td>example</td></tr></tbody></table>
さて、早速接続の処理を書いてみましょう。
<pre><code class="hljs language-python">import mysql.connectorcnx = mysql.connecor.connect(
 user='root',
 host='127.0.0.1',
 password='**********',
 database='example',
 )
</code></pre>
これだけで接続は完了です。（パスワード等が全て正しければ）
他の処理等も終わったら、しっかり切断しておきましょう。
<pre><code class="hljs language-python"> cnx.close()
</code></pre>
これで接続と切断の処理は完了です。
また、hostが127.0.0.1であることは、他のポートとぶつかっているために人為的にローカルホストやポート番号を変えていなければ、デフォルトの位置になっているはずなので、以下でも実行可能です。
<pre><code class="hljs language-python">import mysql.connectorcnx = mysql.connecor.connect(
 user='root',
 host='127.0.0.1',
 password='**********',
 database='example',
 cnx = mysql.connecor.connect(
 user='root',
 password='**********',
 database='example',
 )
</code></pre>
<h2>connectの引数の内容を辞書にまとめておく</h2>
先ほど、引数に入力した情報を辞書にまとめて接続することもできます。
以下のようなスクリプトになります。
<pre><code class="hljs language-python">import mysql.connector

 config = {
 user: 'root',
 host: '127.0.0.1',
 password: '**********',
 database: 'example',
 }
 cnx = mysql.connector.connect(**config)

 cnx.close()
</code></pre>
configは辞書型なので、「**」でアンパックすることも忘れないようにしましょう。
<h2>まとめ</h2>
最終的にまとめると、流れはこんな感じです。
<h2>#辞書使わない</h2>
<pre><code class="hljs language-python">import mysql.connector

cnx = mysql.connecor.connect(
 user=&#x3C;span class="strings">'root',
 host=&#x3C;span class="strings">'127.0.0.1',
 password=&#x3C;span class="strings">'**********',
 database=&#x3C;span class="strings">'example',
)

cnx.close()
</code></pre>
<h2>#辞書型</h2>
<pre><code class="hljs language-python">import mysql.connector

config = {
 user: &#x3C;span class="strings">'root',
 host: &#x3C;span class="strings">'127.0.0.1',
 password: &#x3C;span class="strings">'**********',
 database: &#x3C;span class="strings">'example',
}
cnx = mysql.connector.connect(**config)

cnx.close()
</code></pre>
後ほど、テーブルを実際に作ったり、インサートしたりの方法を紹介しようと思います。

MySQLを使い始めたのですが、Pythonのライブラリとのバージョンの制約が厳しいので、バージョンの確認が必要になりました。その時の備忘録として、対話モードでバージョンを確認する方法を残します。

MySQLの対話モードで使用しているMySQLのバージョンの確認方法です。

MySQLでバージョンを確認する

MySQLを使い始めたのですが、Pythonのライブラリとのバージョンの制約が厳しいので、バージョンの確認が必要になりました。
その時の備忘録として、対話モードでバージョンを確認する方法を残します。
対話モードの開き方から順を追って説明します。
<h2>MySQLで対話モードを起動</h2>
まずははじめに、MySQLの対話モードを起動します。(すでに環境が整っていることを前提)
今回はルートユーザーでパスワードを要する場合を考えます。(自分の設定がそうだからw)
<pre><code class="hljs language-bash">$~ mysql -uroot -p
Enter password: **********
~~ 以下略 ~~

mysql> 
</code></pre>
このように「mysql>」というプロンプトの表示とともに起動するはずです。
<h2>バージョンを確認する</h2>
ここで実際にバージョンを確認してみます。
コマンドは「SELECT VERSION()」です。実際に実行すると以下のようになるはずです。(ちなみに環境はMySQL Ver8.0.19を想定)
<pre><code class="hljs language-sql">mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.19 |
+-----------+
1 row in set (0.01 sec)
</code></pre>
このように、1行目にはコマンド、2行目には実際のバージョン、3行目は実行時間が現れます。
僕は、よく「;」を打ち忘れるのですが、焦らなくても大丈夫。次のように解決が可能です。
<pre><code class="hljs language-sql">mysql> SELECT VERSION()
 -> ;
+-----------+
| VERSION() |
+-----------+
| 8.0.19 |
+-----------+
1 row in set (0.01 sec)
</code></pre>
「;」がないと、まだ入力が続けられるという意味で、 プロンプトが「->」に変化します。その後、焦らず「;」を打てば問題なしです。
<h2>対話モードは制約がゆるい？？</h2>
対話モードにおいては大文字或いは小文字の制約が少しゆるいみたいですね。
実際以下のコマンドでもバージョン確認ができました。
<pre><code class="hljs language-sql">mysql> SELECT VERsion();
mysql> SELECT version();
</code></pre>
とは言っても、Capslockしてしまえば大文字に固定できますので、見にくさもありますし、大文字と小文字の区別は自分の中でやっておくといいでしょう。
<h2>まとめ</h2>
ルートユーザーとして対話モードを起動して、バージョン確認するコマンドはこちらの流れになります。
<pre><code class="hljs language-bash">$~ mysql -uroot -p
Enter password: *******
</code></pre>
mysql> SELECT VERSION();
結果の表示
<pre><code class="hljs">
あとで、ルートユーザー以外でも引数を変えればアクセスできます。 
のちに紹介しようかなと思います。

</code></pre>