データをちょっと取り出して実験したいときに、mysqldumpのSQLを編集するのが面倒だし、DELETEを書くのが面倒だった。
MySQLのテーブルの指定行を様々な形式で取り出したい。コマンドのみでMySQLのテーブルを取り出せる形式をまとめてみた
コマンドで取り出せる形式は次の通り。
これらであれば、簡単にテーブルをSQLとして取り出すことが出来る。
１：テーブルをHTMLのテーブルで取り出す。
mysqlコマンドで結果をHTMLに出力する。
よく見かける例なので有名かも。最近のSQLサーバーなら一般的にサポートされている。
形式
echo "ここがSQL" | mysql -p -u ユーザー名 DB名 -H
- -H
- --html 出力をHTMLテーブルにする。
実例
echo 'set names utf8;select * from Recorder_programTbl where starttime >= "2012-05-01 0:00:00" and starttime < "2012-05-02 0:00:00";' | mysql -p -u root epgrec -H > out.html
出力例
<table border="1"> <tr> <th>id</th> <th>channel_disc</th> <th>channel_id</th> <th>type</th> <th>channel</th> <th>title</th> <th>description</th> <th>category_id</th> <th>starttime</th> <th>endtime</th> <th>program_disc</th> <th>autorec</th> </tr> <tr> <td>300656</td> <td>3001.ontvjapan.com</td> <td>8</td> <td>BS</td> <td>101</td> <td>【二】ＢＳ世界のドキュメンタリー「パレスチナとイスラエルの音楽家たち」（前編）</td> <td>イスラエルとパレスチナに住むさまざまな民族、宗教の音楽家たちによる異例の合同コンサートツアーに密着。政治的対立を乗り越えて共に音楽を奏でるミュージシャンたち。</td> <td>2</td> <td>2012-05-01 00:00:00</td> <td>2012-05-01 00:45:00</td> <td>520eda3a8d5d33ecbfc8fb91dadf2444</td> <td>1</td> </tr>
２：XML形式でテーブルを取り出す
MySQLコマンドの結果をXMLにして取り出す。これも最近のSQLのサーバーだと一般的にサポートされている
解説
echo "ここがSQL" | mysql -p -u ユーザー名 DB名 -X
実例
echo 'set names utf8;select * from Recorder_programTbl where starttime >= "2012-05-01 0:00:00" and starttime < "2012-05-02 0:00:00";' | mysql -p -u root epgrec -X
出力例
取り出したXMLはこんな感じになる。XMLは人が見て読みやすいねぇ。
<?xml version="1.0"?> <resultset statement="select * from Recorder_programTbl where starttime >= "2012-05-01 0:00:00" and starttime < "2012-05-02 0:00:00"" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <row> <field name="id">301087</field> <field name="channel_disc">3004.ontvjapan.com</field> <field name="channel_id">11</field> <field name="type">BS</field> <field name="channel">141</field> <field name="title">Ｑスタイル お得満載</field> <field name="description">世界５か国で展開され多くの人々に受け入られている「ＱＶＣ」のショッピング番組です人気ブランドや本日のお買い得商品が登場！快適なショッピングのスタイルを提案します</field> <field name="category_id">3</field> <field name="starttime">2012-05-01 00:00:00</field> <field name="endtime">2012-05-01 01:00:00</field> <field name="program_disc">78d355cbc545354676a5166847e380ad</field> <field name="autorec">1</field> </row> <row> <field name="id">300656</field> <field name="channel_disc">3001.ontvjapan.com</field> <field name="channel_id">8</field> <field name="type">BS</field> <field name="channel">101</field> <field name="title">【二】ＢＳ世界のドキュメンタリー「パレスチナとイスラエルの音楽家たち」（前編）</field> <field name="description">イスラエルとパレスチナに住むさまざまな民族、宗教の音楽家たちによる異例の合同コンサートツアーに密着。政治的対立を乗り越えて共に音楽を奏でるミュージシャンたち。</field> <field name="category_id">2</field> <field name="starttime">2012-05-01 00:00:00</field> <field name="endtime">2012-05-01 00:45:00</field> <field name="program_disc">520eda3a8d5d33ecbfc8fb91dadf2444</field> <field name="autorec">1</field> </row>
３：テーブルの部分をSQLにする。
SELECTの結果をINSERT文のSQLにしてテーブルを取りだしたい。
mysqlコマンドに --sqlや、--createみたいなoptionがアレばHTML/XMLと同じに出来ていいんだけど、調べてみたら無かった。仕方ないので別の方法を考える。mysqldumpを使えばできることがわかった。
mysqldump -p -u ユーザー名 データベース名 テーブル名 --where 'WHERE条件'
とすれば、SQLが出てくる。
ポイントは --where オプションを使う所
実例
mysqldump --default-character-set=utf8 -p -u root epgrec Recorder_programTbl --where 'starttime >= "2012-05-01 0:00:00" and starttime < "2012-05-02 0:00:00";' --skip-extended-insert --add-drop-table=false -t > out.sql
更に --skip-extended-insert --add-drop-table=false -t を追加して、 insert 文を一行＆１レコードに、Drop Tableを除外、さらにcreate tableを除外している。
実行結果
-- -- Host: localhost Database: epgrec -- ------------------------------------------------------ -- Server version 5.1.54-1ubuntu4-log 6 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; 11 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; 17 -- 19 -- Dumping data for table `Recorder_programTbl` -- -- WHERE: starttime >= "2012-05-01 0:00:00" and starttime < "2012-05-02 0:00:00"; LOCK TABLES `Recorder_programTbl` WRITE; /*!40000 ALTER TABLE `Recorder_programTbl` DISABLE KEYS */; INSERT INTO `Recorder_programTbl` VALUES (301247,'3008.ontvjapan.com',15,'BS','181','サントリーＴＶショッピング 〜トクトク健康セレクション〜','',3,'2012-05-01 00:00:00','2012-05-01 00:30:00','8476dbdaab9fe3b3a74bc5ebbdb69 942',1);
このように綺麗なSQLが出てくる。
５：REPL(CLI) で取得結果を見やすくする（2016-05-29追記
カラムが多すぎて画面で表示できない時は、アスキーアートの表組みではなく、一行に１カラムを表示するモードにすることが出来る。
mysql> select * from rc_logTbl limit 1 offset 99\G *************************** 1. row *************************** id: 258527 logtime: 2016-05-19 00:46:52 level: 0 message: getepg:: 予約ID9123のEPG情報が更新された 1 row in set (0.00 sec)
