それマグで!

知識はカップより、マグでゆっくり頂きます。 takuya_1stのブログ

習慣に早くから配慮した者は、 おそらく人生の実りも大きい。

MySQLのテーブルを好きな形式(XML/HTML/SQL)で取り出したり、データをちょっと取り出して実験したいときのテクニック

データをちょっと取り出して実験したいときに、mysqldumpのSQLを編集するのが面倒だし、DELETEを書くのが面倒だった。
MySQLのテーブルの指定行を様々な形式で取り出したい。コマンドのみでMySQLのテーブルを取り出せる形式をまとめてみた

コマンドで取り出せる形式は次の通り。

  • SQL
  • HTMLテーブル
  • XML
  • テーブルの部分コピー


これらであれば、簡単にテーブルをSQLとして取り出すことが出来る。



1:テーブルを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>【二】BS世界のドキュメンタリー「パレスチナとイスラエルの音楽家たち」(前編)</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>

2:XML形式でテーブルを取り出す

MySQLコマンドの結果をXMLにして取り出す。これも最近のSQLのサーバーだと一般的にサポートされている

解説

echo "ここがSQL" | mysql -p -u ユーザー名 DB名  -X
-X
--xml 出力をXML形式にする。。

実例

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 &gt;= &quot;2012-05-01 0:00:00&quot; and starttime &lt; &quot;2012-05-02 0:00:00&quot;" 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">Qスタイル お得満載</field>
        <field name="description">世界5か国で展開され多くの人々に受け入られている「QVC」のショッピング番組です人気ブランドや本日のお買い得商品が登場!快適なショッピングのスタイルを提案します</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">【二】BS世界のドキュメンタリー「パレスチナとイスラエルの音楽家たち」(前編)</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>

3:テーブルの部分を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 文を一行&1レコードに、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','サントリーTVショッピング 〜トクトク健康セレクション〜','',3,'2012-05-01 00:00:00','2012-05-01 00:30:00','8476dbdaab9fe3b3a74bc5ebbdb69    942',1);

このように綺麗なSQLが出てくる。

4:ちなみにSQLクエリからテーブルを作るには

CREATE table as TABLE_NAME from SELECT * FROM TABLE;

これが手っ取り早い。

5:REPL(CLI) で取得結果を見やすくする(2016-05-29追記

カラムが多すぎて画面で表示できない時は、アスキーアートの表組みではなく、一行に1カラムを表示するモードにすることが出来る。

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)

まとめ

出力形式 使うコマンド
xml mysql -X
html mysql -H
SQL mysqldump --where
テーブルコピー create table from SELECT * from
REPL縦に複数行/G select * from table\G


目的によって使い分けると便利そうですね。

もっとも、大抵の場合は mysqldump で テーブルをSQLに出力する際に--whereを使えるのでこれで殆どの場合が解決しそうです。
SQLからSQLを作れるのが便利ですね。