41から始めました

文字通り41歳から始めたブログです。DB(MySQL)を使ってお仕事してるので、DB周りの話を中心に最近始めたこととかをTwitterのノリで書いています。なお、本サイトにおいて示されている見解は私個人の見解であり、所属団体や組織を代表するものではありません。

データを適当に作成するmysql_random_data_loadを試してみた

MySQL

テストデータ作りたいな

  • 自分で手で作るのもめんどいし、定義変わるとアレ（メンテが必要）やしな。
  • せや、型から適当にデータ作るツールとかないんかな？
  • あったわ→mysql_random_data_load

さすぺる！（さすがPercona！）

試してみよう

use test;
-- 適当なテーブル作る
CREATE TABLE `person` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `guid` varchar(36) COLLATE utf8mb4_bin NOT NULL COMMENT 'GUID',
  `name` varchar(150) COLLATE utf8mb4_bin NOT NULL COMMENT '名前',
  `age` tinyint(4) NOT NULL DEFAULT '0' COMMENT '年齢',
  `money` int(11) DEFAULT NULL COMMENT '手持ちのお金',
  `marriage_flag` tinyint(1) NOT NULL DEFAULT '0' COMMENT '結婚フラグ',
  `prefecture` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '住所（都道府県）',
  `tel_number` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '電話番号',
  `fax_number` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'FAX番号',
  `email` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Ｅメールアドレス',
  `address` varchar(300) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '住所',
  `company` varchar(300) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '会社名',
  PRIMARY KEY (`id`),
  UNIQUE KEY `u_1` (`guid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='誰かさんの情報';

すぐ使いたいんで、precompiled binariesから取ってくる。

解凍して、mysql_random_data_loadを$PATHで見えるところに置いて、

$ which mysql_random_data_load 
/home/xxx/bin/mysql_random_data_load

ってな感じで見えるようになったんでOK。

使い方はhttps://github.com/Percona-Lab/mysql_random_data_loadに書いてある。

デバッグモードで100行くらい入れて試してみるか。

$ mysql_random_data_load test person 100 --debug --user=root --password=xxx --host=127.0.0.1 --port=3307
DEBU[2019-07-06T10:30:48+09:00] &tableparser.Table{
    Schema: "test",
    Name:   "person",
    Fields: {
        {
            TableCatalog:           "def",
            TableSchema:            "test",
            TableName:              "person",
            ColumnName:             "id",
            OrdinalPosition:        1,
            ColumnDefault:          sql.NullString{},
            IsNullable:             false,
            DataType:               "bigint",
            CharacterMaximumLength: sql.NullInt64{},
            CharacterOctetLength:   sql.NullInt64{},
            NumericPrecision:       sql.NullInt64{Int64:19, Valid:true},
            NumericScale:           sql.NullInt64{Int64:0, Valid:true},
            DatetimePrecision:      sql.NullInt64{},
            CharacterSetName:       sql.NullString{},
            CollationName:          sql.NullString{},
            ColumnType:             "bigint(20)",
            ColumnKey:              "PRI",
            Extra:                  "auto_increment",
            Privileges:             "select,insert,update,references",
            ColumnComment:          "ID",
            GenerationExpression:   "",
            SetEnumVals:            {},
            Constraint:             (*tableparser.Constraint)(nil),
            SrsID:                  sql.NullString{},
        },
        {
            TableCatalog:           "def",
            TableSchema:            "test",
            TableName:              "person",
            ColumnName:             "guid",
            OrdinalPosition:        2,
            ColumnDefault:          sql.NullString{},
            IsNullable:             false,
            DataType:               "varchar",
            CharacterMaximumLength: sql.NullInt64{Int64:36, Valid:true},
            CharacterOctetLength:   sql.NullInt64{Int64:144, Valid:true},
            NumericPrecision:       sql.NullInt64{},
            NumericScale:           sql.NullInt64{},
            DatetimePrecision:      sql.NullInt64{},
            CharacterSetName:       sql.NullString{String:"utf8mb4", Valid:true},
            CollationName:          sql.NullString{String:"utf8mb4_bin", Valid:true},
            ColumnType:             "varchar(36)",
            ColumnKey:              "UNI",
            Extra:                  "",
            Privileges:             "select,insert,update,references",
            ColumnComment:          "GUID",
            GenerationExpression:   "",
            SetEnumVals:            {},
            Constraint:             (*tableparser.Constraint)(nil),
            SrsID:                  sql.NullString{},
        },
        {
            TableCatalog:           "def",
            TableSchema:            "test",
            TableName:              "person",
            ColumnName:             "name",
            OrdinalPosition:        3,
            ColumnDefault:          sql.NullString{},
            IsNullable:             false,
            DataType:               "varchar",
            CharacterMaximumLength: sql.NullInt64{Int64:150, Valid:true},
            CharacterOctetLength:   sql.NullInt64{Int64:600, Valid:true},
            NumericPrecision:       sql.NullInt64{},
            NumericScale:           sql.NullInt64{},
            DatetimePrecision:      sql.NullInt64{},
            CharacterSetName:       sql.NullString{String:"utf8mb4", Valid:true},
            CollationName:          sql.NullString{String:"utf8mb4_bin", Valid:true},
            ColumnType:             "varchar(150)",
            ColumnKey:              "",
            Extra:                  "",
            Privileges:             "select,insert,update,references",
            ColumnComment:          "名前",
            GenerationExpression:   "",
            SetEnumVals:            {},
            Constraint:             (*tableparser.Constraint)(nil),
            SrsID:                  sql.NullString{},
        },
        {
            TableCatalog:           "def",
            TableSchema:            "test",
            TableName:              "person",
            ColumnName:             "age",
            OrdinalPosition:        4,
            ColumnDefault:          sql.NullString{String:"0", Valid:true},
            IsNullable:             false,
            DataType:               "tinyint",
            CharacterMaximumLength: sql.NullInt64{},
            CharacterOctetLength:   sql.NullInt64{},
            NumericPrecision:       sql.NullInt64{Int64:3, Valid:true},
            NumericScale:           sql.NullInt64{Int64:0, Valid:true},
            DatetimePrecision:      sql.NullInt64{},
            CharacterSetName:       sql.NullString{},
            CollationName:          sql.NullString{},
            ColumnType:             "tinyint(4)",
            ColumnKey:              "",
            Extra:                  "",
            Privileges:             "select,insert,update,references",
            ColumnComment:          "年齢",
            GenerationExpression:   "",
            SetEnumVals:            {},
            Constraint:             (*tableparser.Constraint)(nil),
            SrsID:                  sql.NullString{},
        },
        {
            TableCatalog:           "def",
            TableSchema:            "test",
            TableName:              "person",
            ColumnName:             "money",
            OrdinalPosition:        5,
            ColumnDefault:          sql.NullString{},
            IsNullable:             false,
            DataType:               "int",
            CharacterMaximumLength: sql.NullInt64{},
            CharacterOctetLength:   sql.NullInt64{},
            NumericPrecision:       sql.NullInt64{Int64:10, Valid:true},
            NumericScale:           sql.NullInt64{Int64:0, Valid:true},
            DatetimePrecision:      sql.NullInt64{},
            CharacterSetName:       sql.NullString{},
            CollationName:          sql.NullString{},
            ColumnType:             "int(11)",
            ColumnKey:              "",
            Extra:                  "",
            Privileges:             "select,insert,update,references",
            ColumnComment:          "手持ちのお金",
            GenerationExpression:   "",
            SetEnumVals:            {},
            Constraint:             (*tableparser.Constraint)(nil),
            SrsID:                  sql.NullString{},
        },
        {
            TableCatalog:           "def",
            TableSchema:            "test",
            TableName:              "person",
            ColumnName:             "marriage_flag",
            OrdinalPosition:        6,
            ColumnDefault:          sql.NullString{String:"0", Valid:true},
            IsNullable:             false,
            DataType:               "tinyint",
            CharacterMaximumLength: sql.NullInt64{},
            CharacterOctetLength:   sql.NullInt64{},
            NumericPrecision:       sql.NullInt64{Int64:3, Valid:true},
            NumericScale:           sql.NullInt64{Int64:0, Valid:true},
            DatetimePrecision:      sql.NullInt64{},
            CharacterSetName:       sql.NullString{},
            CollationName:          sql.NullString{},
            ColumnType:             "tinyint(1)",
            ColumnKey:              "",
            Extra:                  "",
            Privileges:             "select,insert,update,references",
            ColumnComment:          "結婚フラグ",
            GenerationExpression:   "",
            SetEnumVals:            {},
            Constraint:             (*tableparser.Constraint)(nil),
            SrsID:                  sql.NullString{},
        },
        {
            TableCatalog:           "def",
            TableSchema:            "test",
            TableName:              "person",
            ColumnName:             "prefecture",
            OrdinalPosition:        7,
            ColumnDefault:          sql.NullString{},
            IsNullable:             false,
            DataType:               "varchar",
            CharacterMaximumLength: sql.NullInt64{Int64:30, Valid:true},
            CharacterOctetLength:   sql.NullInt64{Int64:120, Valid:true},
            NumericPrecision:       sql.NullInt64{},
            NumericScale:           sql.NullInt64{},
            DatetimePrecision:      sql.NullInt64{},
            CharacterSetName:       sql.NullString{String:"utf8mb4", Valid:true},
            CollationName:          sql.NullString{String:"utf8mb4_bin", Valid:true},
            ColumnType:             "varchar(30)",
            ColumnKey:              "",
            Extra:                  "",
            Privileges:             "select,insert,update,references",
            ColumnComment:          "住所（都道府県）",
            GenerationExpression:   "",
            SetEnumVals:            {},
            Constraint:             (*tableparser.Constraint)(nil),
            SrsID:                  sql.NullString{},
        },
        {
            TableCatalog:           "def",
            TableSchema:            "test",
            TableName:              "person",
            ColumnName:             "tel_number",
            OrdinalPosition:        8,
            ColumnDefault:          sql.NullString{},
            IsNullable:             false,
            DataType:               "varchar",
            CharacterMaximumLength: sql.NullInt64{Int64:20, Valid:true},
            CharacterOctetLength:   sql.NullInt64{Int64:80, Valid:true},
            NumericPrecision:       sql.NullInt64{},
            NumericScale:           sql.NullInt64{},
            DatetimePrecision:      sql.NullInt64{},
            CharacterSetName:       sql.NullString{String:"utf8mb4", Valid:true},
            CollationName:          sql.NullString{String:"utf8mb4_bin", Valid:true},
            ColumnType:             "varchar(20)",
            ColumnKey:              "",
            Extra:                  "",
            Privileges:             "select,insert,update,references",
            ColumnComment:          "電話番号",
            GenerationExpression:   "",
            SetEnumVals:            {},
            Constraint:             (*tableparser.Constraint)(nil),
            SrsID:                  sql.NullString{},
        },
        {
            TableCatalog:           "def",
            TableSchema:            "test",
            TableName:              "person",
            ColumnName:             "fax_number",
            OrdinalPosition:        9,
            ColumnDefault:          sql.NullString{},
            IsNullable:             false,
            DataType:               "varchar",
            CharacterMaximumLength: sql.NullInt64{Int64:20, Valid:true},
            CharacterOctetLength:   sql.NullInt64{Int64:80, Valid:true},
            NumericPrecision:       sql.NullInt64{},
            NumericScale:           sql.NullInt64{},
            DatetimePrecision:      sql.NullInt64{},
            CharacterSetName:       sql.NullString{String:"utf8mb4", Valid:true},
            CollationName:          sql.NullString{String:"utf8mb4_bin", Valid:true},
            ColumnType:             "varchar(20)",
            ColumnKey:              "",
            Extra:                  "",
            Privileges:             "select,insert,update,references",
            ColumnComment:          "FAX番号",
            GenerationExpression:   "",
            SetEnumVals:            {},
            Constraint:             (*tableparser.Constraint)(nil),
            SrsID:                  sql.NullString{},
        },
        {
            TableCatalog:           "def",
            TableSchema:            "test",
            TableName:              "person",
            ColumnName:             "email",
            OrdinalPosition:        10,
            ColumnDefault:          sql.NullString{},
            IsNullable:             false,
            DataType:               "varchar",
            CharacterMaximumLength: sql.NullInt64{Int64:255, Valid:true},
            CharacterOctetLength:   sql.NullInt64{Int64:1020, Valid:true},
            NumericPrecision:       sql.NullInt64{},
            NumericScale:           sql.NullInt64{},
            DatetimePrecision:      sql.NullInt64{},
            CharacterSetName:       sql.NullString{String:"utf8mb4", Valid:true},
            CollationName:          sql.NullString{String:"utf8mb4_bin", Valid:true},
            ColumnType:             "varchar(255)",
            ColumnKey:              "",
            Extra:                  "",
            Privileges:             "select,insert,update,references",
            ColumnComment:          "Ｅメールアドレス",
            GenerationExpression:   "",
            SetEnumVals:            {},
            Constraint:             (*tableparser.Constraint)(nil),
            SrsID:                  sql.NullString{},
        },
        {
            TableCatalog:           "def",
            TableSchema:            "test",
            TableName:              "person",
            ColumnName:             "address",
            OrdinalPosition:        11,
            ColumnDefault:          sql.NullString{},
            IsNullable:             false,
            DataType:               "varchar",
            CharacterMaximumLength: sql.NullInt64{Int64:300, Valid:true},
            CharacterOctetLength:   sql.NullInt64{Int64:1200, Valid:true},
            NumericPrecision:       sql.NullInt64{},
            NumericScale:           sql.NullInt64{},
            DatetimePrecision:      sql.NullInt64{},
            CharacterSetName:       sql.NullString{String:"utf8mb4", Valid:true},
            CollationName:          sql.NullString{String:"utf8mb4_bin", Valid:true},
            ColumnType:             "varchar(300)",
            ColumnKey:              "",
            Extra:                  "",
            Privileges:             "select,insert,update,references",
            ColumnComment:          "住所",
            GenerationExpression:   "",
            SetEnumVals:            {},
            Constraint:             (*tableparser.Constraint)(nil),
            SrsID:                  sql.NullString{},
        },
        {
            TableCatalog:           "def",
            TableSchema:            "test",
            TableName:              "person",
            ColumnName:             "company",
            OrdinalPosition:        12,
            ColumnDefault:          sql.NullString{},
            IsNullable:             false,
            DataType:               "varchar",
            CharacterMaximumLength: sql.NullInt64{Int64:300, Valid:true},
            CharacterOctetLength:   sql.NullInt64{Int64:1200, Valid:true},
            NumericPrecision:       sql.NullInt64{},
            NumericScale:           sql.NullInt64{},
            DatetimePrecision:      sql.NullInt64{},
            CharacterSetName:       sql.NullString{String:"utf8mb4", Valid:true},
            CollationName:          sql.NullString{String:"utf8mb4_bin", Valid:true},
            ColumnType:             "varchar(300)",
            ColumnKey:              "",
            Extra:                  "",
            Privileges:             "select,insert,update,references",
            ColumnComment:          "会社名",
            GenerationExpression:   "",
            SetEnumVals:            {},
            Constraint:             (*tableparser.Constraint)(nil),
            SrsID:                  sql.NullString{},
        },
    },
    Indexes: {
        "u_1": {
            Name:    "u_1",
            Fields:  {"guid"},
            Unique:  true,
            Visible: true,
        },
        "PRIMARY": {
            Name:    "PRIMARY",
            Fields:  {"id"},
            Unique:  true,
            Visible: true,
        },
    },
    Constraints: {
    },
    Triggers: {
    },
    conn: (*sql.DB)(nil),
} 
INFO[2019-07-06T10:30:48+09:00] Starting                                     
DEBU[2019-07-06T10:30:48+09:00] Must run 1 bulk inserts having 100 rows each 
INFO[2019-07-06T10:30:49+09:00] 100 rows inserted

お、入ってる感じやな。どれどれ？

id guid name age money marriage_flag prefecture tel_number fax_number email address company
1 recusandae et consequatur eius aut a consequatur nihil error eius ut. 5 1342458479 6 rerum unde qui perferendis nob Terry Sanchez Tammy Torres eum culpa nam minima maxime. et non nisi voluptas temporibus aliquam magnam. eius deleniti nam et sint voluptates id.
2 aut odio soluta ut dolores. omnis accusamus est voluptatem consequuntur quisquam quae quia. 11 914091476 5 fugiat magnam aut culpa ut rep Janet Oliver Juan King nemo suscipit doloremque porro error et qui tenetur ut asperiores corporis! nemo quia magnam hic dolor. adipisci architecto animi vitae deleniti rerum.
3 aut dolor consequatur soluta exercit perspiciatis sit odio fugit quasi non. 3 1255569388 11 ullam facere exercitationem il Janet Murphy Fred Grant ut ea nisi recusandae quo inventore tenetur et. et blanditiis maxime est quam quidem id. illo ea voluptas vitae repudiandae architecto saepe.
4 quidem omnis iste ea a impedit odio quod velit ut earum dolore molestiae! 14 2003588754 12 et qui et id voluptas voluptat Tina Garrett I II II Joe Edwards quo explicabo eum qui alias sequi. dolorem quaerat nihil aut perspiciatis qui asperiores nihil. alias nisi ullam quo debitis iusto animi.
5 illo earum est pariatur aut in digni consequatur molestias dicta omnis. 4 1698116023 5 doloribus natus distinctio nos Brenda Hunt Sean Carter assumenda et deserunt quidem. consequatur occaecati non libero accusantium reprehenderit quam suscipit. ratione eum beatae laboriosam quo enim.
6 consequuntur quibusdam nihil soluta accusamus similique cum eos quidem. 8 1153628287 1 voluptas est et temporibus aut Paula Parker Kathryn Watkins quis voluptatem at perferendis labore. reiciendis voluptates itaque voluptatem optio ea qui hic. ab quibusdam illum.
7 voluptas animi vero tenetur adipisci et ut et! 5 1640670520 8 quod corporis qui rerum culpa Joshua Hart Louise Fernandez quia et molestiae distinctio quaerat quasi soluta aut voluptatem et recusandae! doloribus dignissimos ut hic tempora. temporibus eveniet vel quisquam repellendus aut animi fugit.
8 quae quod ratione pariatur repellend suscipit omnis aut consequatur numquam laboriosam non repellendus. 11 1218842385 7 dolorem quia qui accusamus eni Kenneth Burns Terry Bryant consequatur ex aliquam beatae qui et consequatur et. vitae ratione quaerat rerum facilis. animi sit ut nobis quae ea.
9 laudantium odit et facere minima. ea quis voluptas ipsum dignissimos asperiores sequi aut. 13 601894044 12 id non consequuntur iste disti Charles Anderson Raymond Diaz sequi impedit nihil ea vel et nihil rerum aut. sit commodi et autem eius. odit qui vitae facilis modi quia harum amet aspernatur.
10 magni ullam dignissimos praesentium dicta qui sunt dolor ut repellat hic. 10 1408984034 11 quia tempora consequatur delec Stephanie Garcia Patricia Carroll repudiandae fuga dignissimos odio voluptate quia. quia necessitatibus molestiae dolorem natus accusantium. sunt consectetur est amet doloremque qui autem quae.

そりゃそうだよね・・・。

だって型で見てるだけなんだもん。

カラム名も見てくれるツール欲しい！

  • 日本語で入ってほしいものもあるよね。
  • コメントを参考にしたりしてほしいよね。
  • MySQL8.0でも動いて欲しい（このツールはMySQL5.7までしか動かない）

自分で作るか、こいつをいじるかな〜

追記

github.com

を見るとわかりますが、型のフィールド値に制限が有ります。

decimal(25,0)

とかやると、

panic: invalid argument to Int63n

ってメッセージが出るのでご注意を！