What's New Qiita

20190203のMySQLに関する記事は5件です。

  • 投稿日：2019-02-03T20:32:20+09:00

ラズパイをいじる ～LAMP環境編～

前書き

前回のOSインストール編からの続きです。
Teratermでつないだ状態から始めます。

■LAMP環境構築編

□参考記事

インストール内容

  • Apache2.4
  • PHP7.0
  • MySQL

手順

1.Apache

インストール

sudo apt-get install apache2

バージョン確認

apache2 -v
Server version: Apache/2.4.25 (Raspbian)

デフォルトのドキュメントルート

/var/www/html

Apacheの自動起動確認

sudo ls /etc/rc2.d/

2. PHP7

インストール

sudo apt-get install php7.0 libapache2-mod-php7.0 php7.0-mbstring php7.0-gettext php7.0-mysql

バージョン確認

php -v

php.iniを2か所編集

mbstring.language = Japanese
mbstring.internal_encoding = UTF-8

3. MySQL

インストール

sudo apt-get install mysql-serever

文字コードの設定

sudo vi /etc/mysql/my.conf

# 設定ファイルに以下を追加
[mysqld]
character-set-server=utf8 # デフォルトの文字コードをutf-8
skip-character-set-client-handshake # クライアントのリクエストが何であれutf-8で返す

あとがき

ラズパイならではの情報が特にあるわけではなく、
debianのサーバー立てるみたいになってしまってますね。
途中省略しましたがvim入れたり、rootにパスワードを設定したりしてます。
次回はUSBからbootとストレージの拡張とかやろうかと思います。

補足・修正等ございましたらお知らせください。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2019-02-03T20:30:09+09:00

LaravelのMigrationでカラムの追加

Laravelのデータベース設計を更新したい

最近、Laravelで新規のWebアプリケーションを製作しております。
MySQLを選択してMigrationでデータベースの設計をやっており、データの挿入をしていましたが、
追加したいカラムが出てきました。

mysql> desc stadium_posts;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| stadium    | varchar(255)     | NO   |     | NULL    |                |
| latitude   | double           | NO   |     | NULL    |                |
| longitude  | double           | NO   |     | NULL    |                |
| league     | varchar(255)     | NO   |     | NULL    |                |
| address    | varchar(255)     | NO   |     | NULL    |                |
| created_at | timestamp        | YES  |     | NULL    |                |
| updated_at | timestamp        | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
8 rows in set (0.03 sec)


$ php artisan tinker
>>> App\StadiumPost::all()->toArray();
=> [
     [
       "id" => 1,
       "stadium" => "阪神甲子園球場",
       "latitude" => 34.721394,
       "longitude" => 135.361594,
       "league" => "プロ野球",
       "address" => "兵庫県西宮市甲子園町1番82号",
       "created_at" => "2019-02-02 10:37:56",
       "updated_at" => "2019-02-02 10:45:26",
     ],
     [
       "id" => 2,
       "stadium" => "東京ドーム",
       "latitude" => 35.703667,
       "longitude" => 139.753393,
       "league" => "プロ野球",
       "address" => "東京都文京区後楽１丁目３－６１",
       "created_at" => "2019-02-02 10:52:41",
       "updated_at" => "2019-02-02 10:52:41",
     ],
     [
       "id" => 3,
       "stadium" => "ナゴヤドーム",
       "latitude" => 35.170915,
       "longitude" => 136.881537,
       "league" => "プロ野球",
       "address" => "愛知県名古屋市東区大幸南1丁目1-1",
       "created_at" => "2019-02-02 11:05:36",
       "updated_at" => "2019-02-02 11:05:36",
     ],
     [
       "id" => 6,
       "stadium" => "明治神宮野球場",
       "latitude" => 35.674572,
       "longitude" => 139.717136,
       "league" => "プロ野球",
       "address" => "東京都新宿区霞ヶ丘町3番1号",
       "created_at" => "2019-02-02 11:09:29",
       "updated_at" => "2019-02-02 11:09:29",
     ],
     [
       "id" => 8,
       "stadium" => "横浜スタジアム",
       "latitude" => 35.443428,
       "longitude" => 139.6401,
       "league" => "プロ野球",
       "address" => "神奈川県横浜市中区横浜公園",
       "created_at" => "2019-02-02 11:11:30",
       "updated_at" => "2019-02-02 11:11:30",
     ],
     [
       "id" => 9,
       "stadium" => "MAZDA Zoom-Zoom スタジアム広島",
       "latitude" => 34.392938,
       "longitude" => 132.458942,
       "league" => "プロ野球",
       "address" => "広島県広島市中区本通８−２４",
       "created_at" => "2019-02-02 11:14:15",
       "updated_at" => "2019-02-02 11:14:15",
     ],
   ]

今のデータベースのカラムに以下のことをやりたいと思います。

  • カラムにスタジアムのある国のcountryを追加

　Migrationファイルの作成・実行

参考にしたサイト
https://laraweb.net/knowledge/2251/

artisanコマンドでデータベース更新用のMigrationファイルを作成します

$ php artisan make:migration update_stadiumpost_table --table=StadiumPost
Created Migration: 2019_02_03_190732_update_stadiumpost_table

Migrationファイルを以下のように編集しました

2019_02_03_190732_update_stadiumpost_table.php
<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class UpdateStadiumpostTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('stadium_posts', function (Blueprint $table) {
            // countryカラム追加
            $table->string('country');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('stadium_posts', function (Blueprint $table) {
            //
        });
    }
}

php artisan migrateでマイグレーションが実行され、以下の通りカラムが追加されました。

mysql> desc stadium_posts;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| stadium    | varchar(255)     | NO   |     | NULL    |                |
| latitude   | double           | NO   |     | NULL    |                |
| longitude  | double           | NO   |     | NULL    |                |
| league     | varchar(255)     | NO   |     | NULL    |                |
| address    | varchar(255)     | NO   |     | NULL    |                |
| created_at | timestamp        | YES  |     | NULL    |                |
| updated_at | timestamp        | YES  |     | NULL    |                |
| country    | varchar(255)     | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
9 rows in set (0.01 sec)

$ php artisan tinker
>>> App\StadiumPost::all()->toArray();
=> [
     [
       "id" => 1,
       "stadium" => "阪神甲子園球場",
       "latitude" => "34.721394",
       "longitude" => "135.361594",
       "league" => "プロ野球",
       "address" => "兵庫県西宮市甲子園町1番82号",
       "created_at" => "2019-02-02 10:37:56",
       "updated_at" => "2019-02-02 10:45:26",
       "country" => "",
     ],
     [
       "id" => 2,
       "stadium" => "東京ドーム",
       "latitude" => "35.703667",
       "longitude" => "139.753393",
       "league" => "プロ野球",
       "address" => "東京都文京区後楽１丁目３－６１",
       "created_at" => "2019-02-02 10:52:41",
       "updated_at" => "2019-02-02 10:52:41",
       "country" => "",
     ],
     [
       "id" => 3,
       "stadium" => "ナゴヤドーム",
       "latitude" => "35.170915",
       "longitude" => "136.881537",
       "league" => "プロ野球",
       "address" => "愛知県名古屋市東区大幸南1丁目1-1",
       "created_at" => "2019-02-02 11:05:36",
       "updated_at" => "2019-02-02 11:05:36",
       "country" => "",
     ],
     [
       "id" => 6,
       "stadium" => "明治神宮野球場",
       "latitude" => "35.674572",
       "longitude" => "139.717136",
       "league" => "プロ野球",
       "address" => "東京都新宿区霞ヶ丘町3番1号",
       "created_at" => "2019-02-02 11:09:29",
       "updated_at" => "2019-02-02 11:09:29",
       "country" => "",
     ],
     [
       "id" => 8,
       "stadium" => "横浜スタジアム",
       "latitude" => "35.443428",
       "longitude" => "139.6401",
       "league" => "プロ野球",
       "address" => "神奈川県横浜市中区横浜公園",
       "created_at" => "2019-02-02 11:11:30",
       "updated_at" => "2019-02-02 11:11:30",
       "country" => "",
     ],
     [
       "id" => 9,
       "stadium" => "MAZDA Zoom-Zoom スタジアム広島",
       "latitude" => "34.392938",
       "longitude" => "132.458942",
       "league" => "プロ野球",
       "address" => "広島県広島市中区本通８−２４",
       "created_at" => "2019-02-02 11:14:15",
       "updated_at" => "2019-02-02 11:14:15",
       "country" => "",
     ],
   ]
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2019-02-03T15:03:14+09:00

MySQL ストアドプロシージャでテストデータ作成

実行計画を取りたくて、ストアドプロシージャでテストデータを作成した時のメモ。
MySQLのバージョンは8.0。

やりたいこと

予定情報が入っているSCHEDULEテーブルに、ランダムなテストデータを大量に投入したい。

テーブル定義

SCHEDULEテーブルの定義は以下の通りです。

+-----------------+-------------+------+-----+---------+----------------+
| Field           | Type        | Null | Key | Default | Extra          |
+-----------------+-------------+------+-----+---------+----------------+
| SCHEDULE_ID     | int(11)     | NO   | PRI | NULL    | auto_increment |
| TITLE           | varchar(10) | NO   |     | NULL    |                |
| START_DATE_TIME | datetime    | NO   |     | NULL    |                |
| END_DATE_TIME   | datetime    | NO   |     | NULL    |                |
+-----------------+-------------+------+-----+---------+----------------+

主キーのSCHEDULE_IDが連番で振られ、予定のタイトル（TITLE）と開始日時（START_DATE_TIME）、終了日時（END_DATE_TIME）が格納されます。

テーブル作成クエリ

CREATE TABLE SCHEDULE
    (SCHEDULE_ID INT, TITLE VARCHAR(10), START_DATE_TIME DATETIME, END_DATE_TIME DATETIME);

ALTER TABLE SCHEDULE ADD PRIMARY KEY(SCHEDULE_ID);
ALTER TABLE SCHEDULE MODIFY SCHEDULE_ID INT AUTO_INCREMENT;
ALTER TABLE SCHEDULE MODIFY TITLE VARCHAR(10) NOT NULL;
ALTER TABLE SCHEDULE MODIFY START_DATE_TIME DATETIME NOT NULL;
ALTER TABLE SCHEDULE MODIFY END_DATE_TIME DATETIME NOT NULL;

格納データ例

+-------------+--------+---------------------+---------------------+
| SCHEDULE_ID | TITLE  | START_DATE_TIME     | END_DATE_TIME       |
+-------------+--------+---------------------+---------------------+
|           1 | 焼肉   | 2019-01-20 19:00:00 | 2019-01-20 21:00:00 |
|           2 | 温泉   | 2019-01-23 20:00:00 | 2019-01-23 22:00:00 |
|           3 | 飲み会 | 2019-02-01 19:00:00 | 2019-02-01 22:00:00 |
+-------------+--------+---------------------+---------------------+

ストアドプロシージャ作成

ここからが本題。
MySQLでは実行クエリが書かれたストアドプロシージャを作り、それを呼び出すことでクエリを実行することができる。
プロシージャ内でWHILEを使って繰り返し処理を記述することができ、大量のデータ投入を自動化できるのでとても便利。

構文

プロシージャを作成する際はCREATE PROCEDUREを使用する。
構文はざっくり以下の通り。

CREATE PROCEDURE プロシージャ名 ([引数[,...]])
BEGIN
    処理内容
END
  • プロシージャ名
    • プロシージャ名を記載する。呼び出す際はここで定義されたプロシージャ名を指定する必要がある。
  • 引数
    • 処理内で使用する引数、戻り値として受け取る変数などを指定する。
  • 処理内容
    • 実際の処理内容のクエリを記載（SELECTやINSERTなど）。

設計

  • プロシージャ名
    • insert_schedule
  • 引数
    • 作成する予定の数。引数名はcountとする。
  • 処理内容
    • countで指定した数の予定を作成する。各カラム値は以下の通り。
      • TITLE
        • 10文字以内のランダムな文字列
      • START_DATE_TIME
        • 現在から大体100日以内。
      • END_DATE_TIME
        • 開始日時から100分以内。

実装

作成したプロシージャは以下の通り。

DELIMITER //
CREATE PROCEDURE insert_schedule(IN count INT)
BEGIN
    DECLARE i INT;

    SET i = 0;
    WHILE i < count DO
        INSERT INTO SCHEDULE (
            TITLE,
            START_DATE_TIME,
            END_DATE_TIME
        )
        VALUES (
            LEFT(MD5(RAND()), RAND() * 10),
            DATE_FORMAT(NOW() + INTERVAL RAND() * 100 DAY + INTERVAL RAND() * 100 HOUR + INTERVAL RAND() * 100 MINUTE, '%Y-%m-%d %H:%i'),
            START_DATE_TIME + INTERVAL RAND() * 100 MINUTE
        );

        SET i = i + 1;

    END WHILE;

END//
DELIMITER ;

処理的な解説

DELIMITER

MySQLはデフォルトの区切り文字が;なので、DELIMITERを変更せずにこのクエリを流すと最初のDECLARE i INT;で一旦クエリが終了しエラーになります。
なので、最初にDELIMITER //で区切り文字を変更し、CREATE PROCEDUREを全部読み終わったあとにDELIMITER ;で区切り文字を戻しています。

WHILE

WHILE i < count DOで引数にセットしたcountの数だけINSERTを繰り返しています。カウンタ変数はiで、WHILEの前に0で初期化し、毎回インクリメントしています。

データ的な解説

TITLE

10文字以内のランダムな文字列を作成しています。
MD5(RAND())でランダムな文字列を作成し、LEFT()でその文字列を指定した長さでカットしています。長さはRAND() * 10で10文字以内の長さを作っています。

START_DATE_TIME

現在日時をNOW()で取得し、適当な日時分をINTERVAL ～で加算しています。秒以下の情報はいらなかったのでDATE_FORMAT()で削っています。

END_DATE_TIME

START_DATE_TIMEに100分以内の時間をINTERVAL RAND() * 100 MINUTEで加算しています。

ストアドプロシージャ実行

作成したプロシージャは以下のようにCALLで呼び出します。

CALL insert_schedule(10);

ランダムな感じのデータが作成されました。

+-------------+-----------+---------------------+---------------------+
| SCHEDULE_ID | TITLE     | START_DATE_TIME     | END_DATE_TIME       |
+-------------+-----------+---------------------+---------------------+
|           1 | 092       | 2019-04-25 02:01:00 | 2019-04-25 02:46:00 |
|           2 | d8378c    | 2019-04-02 13:59:00 | 2019-04-02 15:22:00 |
|           3 | 2a        | 2019-04-04 13:41:00 | 2019-04-04 14:21:00 |
|           4 | 2cfd1     | 2019-03-13 08:16:00 | 2019-03-13 08:51:00 |
|           5 |           | 2019-02-18 23:25:00 | 2019-02-18 23:57:00 |
|           6 | 7         | 2019-02-12 15:17:00 | 2019-02-12 16:48:00 |
|           7 | 01        | 2019-04-26 23:04:00 | 2019-04-27 00:43:00 |
|           8 | 656c882c3 | 2019-03-06 19:17:00 | 2019-03-06 19:54:00 |
|           9 | d80       | 2019-03-15 00:21:00 | 2019-03-15 00:58:00 |
|          10 | cc93316   | 2019-04-20 17:16:00 | 2019-04-20 17:19:00 |
+-------------+-----------+---------------------+---------------------+

参考

MySQLで簡単にランダムなテストデータを作成する方法
13.1.17 CREATE PROCEDURE and CREATE FUNCTION Syntax

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2019-02-03T13:18:15+09:00

Mysql2::Error: Specified key was too long; max key length is 767 bytesを解決する

現象

utf8mb4のカラム（name）に対してindexをはるときにエラーになってしまった:innocent:

...
-- add_index("posts", ["name"], {:name=>"name_index"})
[ERROR] Mysql2::Error: Specified key was too long; max key length is 767 bytes: CREATE  INDEX `name_index`  ON `posts` (`name`)
...

このあたりの記事を見ると、いろんな方法があるみたい :thinking:

対応

今回はcnfを書き換えてDBごと対応することにします。

cnfファイルの場所はmysql --help | grep my.cnfなどで調べます :point_up:

/usr/local/etc/my.cnf
...
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix

homebrewでインストールしたmysql@5.6を使っていたのでbrew servicesコマンドで再起動。

brew services restart mysql@5.6

以上です :hugging:

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2019-02-03T00:03:59+09:00

XML形式のデータをMySQLにロードする

Q. mysqldumpで生成したxml形式のダンプデータをリストアしたい。
A. LOAD XML 構文を使えばできる。

mysqlを起動し、ログイン

$ ls  #xmlファイルが存在することを確認
person.xml
$ mysql.server start 
$ mysql -u [ユーザ名] -p

XMLファイルの中身はこんな感じ(mysqldumpで作成できる)

person.xml
<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="practice">
    <table_structure name="person">
        <field Field="id" Type="int(11)" Null="YES" Key="" Extra="" Comment="" />
        <field Field="name" Type="varchar(255)" Null="YES" Key="" Extra="" Comment="" />
        <options Name="person" Engine="InnoDB" Version="10" Row_format="Dynamic" Rows="2" Avg_row_length="8192" Data_length="16384" Max_data_length="0" Index_length="0" Data_free="0" Create_time="2019-02-02 14:49:49" Update_time="2019-02-02 14:55:38" Collation="utf8_general_ci" Create_options="" Comment="" />
    </table_structure>
    <table_data name="person">
    <row>
        <field name="id">1</field>
        <field name="name">usera</field>
    </row>
    <row>
        <field name="id">2</field>
        <field name="name">userb</field>
    </row>
    </table_data>
</database>
</mysqldump>

XMLファイルをMySQLにロードする

personテーブルのデータをリストアする

mysql> USE [データベース名];
mysql> TRUNCATE person; 
mysql> LOAD XML LOCAL INFILE 'person.xml' INTO TABLE person;

結果

mysql> SELECT * FROM person;

+------+-------+
| id   | name  |
+------+-------+
|    1 | usera |
|    2 | userb |
+------+-------+
2 rows in set (0.01 sec)

参考

https://dev.mysql.com/doc/refman/5.6/ja/load-xml.html

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

日付別

タグ一覧