mysql のデータ構造をselectしてスプレッドシートで幸せになりたい(備忘録)
TypeScriptで型定義を書いたりする時に
データベースの型定義がスプレッドシートにあれば・・・幸せになれそう。
みんな思ってると思う!きっとそうw
ということで何がほしいか
これをスプレッドシートに出したい!
で考えた、というかGoogle先生が頑張ったw
SQL
ちなみにデータベース名が「test」とする
出力したくないテーブルは最後の方のコメントアウトを解除
SELECT
CONCAT(TABLE_NAME,'____',COLUMN_NAME) AS 'FIND_KEY'
,TABLE_NAME AS 'TableName'
,ORDINAL_POSITION AS 'No'
,COLUMN_NAME AS 'ColumnName'
,COLUMN_COMMENT AS 'ColumnNameText'
,DATA_TYPE AS 'DataType'
,case
when DATA_TYPE LIKE '%time'
OR DATA_TYPE LIKE 'time'
OR DATA_TYPE LIKE 'timestamp'
then DATETIME_PRECISION
when DATA_TYPE LIKE '%decimal%'
then concat(NUMERIC_PRECISION,',',NUMERIC_SCALE)
when DATA_TYPE LIKE '%int%'
OR DATA_TYPE LIKE 'bit'
OR DATA_TYPE LIKE 'float'
OR DATA_TYPE LIKE 'double'
then NUMERIC_PRECISION
when DATA_TYPE LIKE '%char%'
OR DATA_TYPE LIKE '%text%'
OR DATA_TYPE LIKE '%binary'
OR DATA_TYPE LIKE '%blob'
then CHARACTER_MAXIMUM_LENGTH
when DATA_TYPE LIKE 'year'
then CAST(REPLACE(REPLACE(REPLACE(COLUMN_TYPE,'(',''),')',''),DATA_TYPE,'')AS SIGNED)
ELSE
null
END AS 'Length'
,case
when COLUMN_TYPE LIKE '%nsigned%'
then 1
ELSE 0
END AS 'Unsigned'
,case
when IS_NULLABLE = 'YES'
then 1
ELSE 0
END AS 'IsNullable'
,case
when COLUMN_TYPE LIKE '%zerofill%'
then 1
ELSE 0
END AS 'Zerofill'
,case
when COLUMN_DEFAULT IS NULL
and IS_NULLABLE = 'YES'
then 'null'
when COLUMN_DEFAULT IS NULL
and IS_NULLABLE = 'NO'
then 'unset'
ELSE 'value'
END AS 'DefaultType'
,case
when COLUMN_DEFAULT IS NULL
and IS_NULLABLE = 'YES'
then ''
when COLUMN_DEFAULT IS NULL
and IS_NULLABLE = 'NO'
then ''
ELSE COLUMN_DEFAULT
END AS 'DefaultValue'
,case
when EXTRA LIKE '%auto_increment%'
then 1
ELSE 0
END AS 'AutoIncrement'
,IFNULL(key_count ,0) AS 'Key'
,IFNULL(key_list ,'') AS 'KeyList'
FROM
INFORMATION_SCHEMA.COLUMNS
LEFT JOIN
(
SELECT
TABLE_NAME AS key_table_name,
COLUMN_NAME AS key_column_name,
COUNT(*) AS key_count,
GROUP_CONCAT(
CASE
WHEN NON_UNIQUE=1 THEN "K"
WHEN NULLABLE LIKE "YES" THEN "U"
ELSE "P"
END
SEPARATOR ','
) AS key_list
FROM INFORMATION_SCHEMA.STATISTICS
WHERE
TABLE_SCHEMA LIKE 'test'
GROUP BY TABLE_NAME, COLUMN_NAME
)AS a1 ON
a1.key_table_name=TABLE_NAME and
a1.key_column_name=COLUMN_NAME
WHERE
TABLE_SCHEMA LIKE 'test'
-- and TABLE_NAME NOT LIKE '%_log%'
ORDER BY TABLE_NAME,ordinal_position
結果は?
一応一番左の列はスプレッドシートでVlookupのための目印です。深い意味はありませんw
さて・・・
すみません。さきに白状します。まだ不完全ですぅ(´﹃`)
これでもそこそこ便利なのですが、
改善したいポイント
- インデックスの情報は別にリストで出すべきだと考える
- そもそもプライマリーとユニークキーが微妙にうまく変換できてない
- 普段使わない型が未対応
スプレッドシートに投げる方法
- HeidiSQL等でSelectして出力データのエクスポートからのコピペッ!(無難)
- スプレッドシート側にPOSTの受けを用意する
- 稼働するサーバーから特定のスプレッドシートに対してリクエスト投げる形でヘッダー等に認証になりそうななにか含めて・・・
- 一人でアプリ全部つくる状況だとめちゃくちゃ多様する
あとは
スプレッドシートの関数職人になりましょう。(以上)
タイトルスプレッドシートだけど一切出てきてないので
次はスプレッドシートでこのデータを受ける部分を・・・・・そのうち