What's New Qiita

20191004のMySQLに関する記事は9件です。

  • 投稿日：2019-10-04T23:49:37+09:00

Mysql:AutoIncrementIdを推測するワザ

概要

バイナリサーチでAIDを推測するだけ

ありがちな遅いクエリ

SELECT *
FROM `sugoi_table`
WHERE `create_time` >= '2019-10-01'
(48,126 合計, クエリの実行時間 28.3798 秒)

Explainの結果

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE sugoi_table range PRIMARY PRIMARY 4 NULL 32715643 Using where

rows(走査範囲)が多すぎる!!

  • MySQLは「1番目のレコードから全てのレコードのcreate_timeが10/1より大きいか？」を判断するので大変遅い
  • 本番write用のDBにこんなもん流した日にはしばらくサービスが反応しなくなる
  • せめて迂闊なselectを投げる前にindexが効いてるか確認しよう・・・

シンプルな解決方法

AIDで走査範囲を絞る

SELECT *
FROM `sugoi_table`
WHERE `user_id` >=50000000
AND `create_time` >= '2019-10-01'
(48,153 合計, クエリの実行時間 0.0621 秒)

早くなった

Explainの結果

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE sugoi_table range PRIMARY PRIMARY 4 NULL 500844 Using where

rowsが3200万→50万に減ったのが要因

  • 適当に作った調査クエリを流して「結果が返ってこない・・・」とかなりそうな時は事前に使おう

だがしかし、それっぽいAIDを調べるのが面倒くさい！！

crate_timeで二分探索(バイナリサーチ)すればいい

アルゴリズムを勉強するなら二分探索から始めよう！ 『なっとく！アルゴリズム』より

文字が読みたくない人向け

  • 全体の中心のデータを見て「大きい」なら後ろ、「小さい」なら前を繰り返して検索する方法
  • 計算量はO(log2 n)だそうな

実装してみる

/**
 * 指定テーブルの指定日付のAIDを推測する
 */
public static function guessAid( $tableName, $targetKey, $targetDateTime) {

    //まず最大のAIDを取得する
    $maxId = self::_getMaxAid($tableName, $targetKey);

    $low    = 1;
    $high   = $maxId;
    $loopCnt = 0;
    $guess  = 0;

    while ( $low <= $high ) {
        $loopCnt++;
        $mid = (int)(($high + $low) / 2); //中央値を作成 floatにならないように
        $sql = "SELECT create_time FROM {$tableName} WHERE {$targetKey} >= {$mid} LIMIT 1";
        $tmpData = Db::query($sql);
        if ( empty($tmpData)) {
            //データが無い時の処理(投げやり);
            break;
        }
        if (strtotime($tmpData['create_time']) < strtotime($targetDateTime)) {
            //時間が小さい時に推測候補を保持しておく
            //逆条件の時にループが終わると指定時間ちょい後のAIDになってしまう場合がある為
            $guess = $low; 
            $low = $mid + 1;
        } else {
            $high = $mid - 1;
        }
        //データが消されている可能性も考慮して一定ループで抜ける
        if ( $loopCnt >= 50 ) {
            break;
        }
    }
    //echo("だいたいこの辺じゃろ");
    $id = $guess;
    return $id;
}

/**
 * テーブルのMAX_AIDをとる
 */
private static function _getMaxAid( $tableName="", $targetKey="") {
    $sql = "SELECT MAX({$targetKey}) FROM {$tableName}";
    $data = Db::query($sql);
    return $data;
}
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2019-10-04T23:12:19+09:00

mysqldumpで最低限必要な権限

結論

GRANT SELECT, SHOW VIEW, TRIGGER ON *.* TO 'ユーザー名'@'接続元';

理由

ダンプされるテーブルに対する SELECT 権限、ダンプされるビューに対する SHOW VIEW、ダンプされるトリガーに対する TRIGGER、および --single-transaction オプションが使用されない場合には LOCK TABLES が少なくとも必要です。

参考：https://dev.mysql.com/doc/refman/5.6/ja/mysqldump.html

備考

特定のDBのみをdumpしたかったものの、
調べていて「そんな権限必要？」ってなったので調べてみました。

きっかけ

DB間で同期(レプリケーションではない)をとりたくて調べていたら、
https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.SmallExisting.html
これが出てきて、適切な権限を付与したくなったので調べてみました。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2019-10-04T22:05:33+09:00

【重要!!】WindowsにXAMPPのインストール方法

1.XAMPPとは

  • XAMPP(ザンプ) とは、ウェブアプリケーションの実行に必要なフリーソフトウェアをパッケージとしてまとめたもの。
  • クロスプラットフォーム(cross(X)-platform) 、 Apache 、 MySQL 、 PHP 、 Perl の頭文字を合わせて XAMPP という。

2.XAMPPをダウンロードする方法

01.png
02.png
03.png

  1. XAMPPの公式サイト へアクセスし、 Windows向けXAMPP をクリックする。
  2. ダウンロードが自動で開始される。
  3. ダウンロードが完了したら、 フォルダ をチェックする。
  4. xampp-windows-x64-?.?.?-?-VC?-installer がダウンロードされていれば成功です。

3.XAMPPをインストールする方法

  1. ダウンロードした xampp-windows-x64-?.?.?-?-VC?-installer を起動する。
  2. Next をクリックする。

04.png
3. 全ての項目にチェックがついてることを確認し、 Next をクリックする。
05.png
4. インストールする フォルダ を選択し、 Next をクリックする。
06.png
5. Next をクリックする。

07.png
6. Next をクリックする。

09.png
7. インストールが開始される。
10.png
8. インストール終了後、チェックをつけ、Finish をクリックする。
11.png
9. アメリカの国旗 を選択し、 Save をクリックする。
12.png
10. XAMPP Control Panel の起動後、 Apach の横にある Start ボタンをクリックする。
13.png
11. XAMPP Control Panel の Apach が緑になり、 Port(s) が設定されれば成功です。
14.png
12. XAMPP をインストールしたフォルダを開き、 htdocs フォルダを開く。
15.png
13. 右クリックし、 新規作成 から フォルダ を選択する。
16.png
14. フォルダ名を php にする。
17.png
15. エディタ(メモ帳) を起動し、下記のコードを入力する。

テストソースコード
<?php
phpinfo();
?>

18.png
16. メニューから ファイル の 名前をつけて保存 をクリックする。
19.png
17. xampp フォルダをクリックする。
20.png
18. htdocs フォルダをクリックする。
21.png
19. php フォルダをクリックする。
22.png
20. ファイル名を index.php で保存する。
23.png
21. http://localhost/php/　にアクセスし、画像のように表示されれば成功です。
24.png

4.まとめ

XAMPPとは

  • XAMPP(ザンプ) とは、ウェブアプリケーションの実行に必要なフリーソフトウェアをパッケージとしてまとめたもの。
  • クロスプラットフォーム(cross(X)-platform) 、 Apache 、 MySQL 、 PHP 、 Perl の頭文字を合わせて XAMPP という。

XAMPPをダウンロードする方法

  1. XAMPPの公式サイト へアクセスし、 Windows向けXAMPP をクリックする。
  2. ダウンロードが自動で開始される。
  3. ダウンロードが完了したら、 フォルダ をチェックする。
  4. xampp-windows-x64-?.?.?-?-VC?-installer がダウンロードされていれば成功です。

XAMPPをインストールする方法

  1. ダウンロードした xampp-windows-x64-?.?.?-?-VC?-installer を起動する。
  2. Next をクリックする。
  3. 全ての項目にチェックがついてることを確認し、 Next をクリックする。
  4. インストールする フォルダ を選択し、 Next をクリックする。
  5. Next をクリックする。
  6. Next をクリックする。
  7. インストールが開始される。
  8. インストール終了後、チェックをつけ、Finish をクリックする。
  9. アメリカの国旗 を選択し、 Save をクリックする。
  10. XAMPP Control Panel の起動後、 Apach の横にある Start ボタンをクリックする。
  11. XAMPP Control Panel の Apach が緑になり、 Port(s) が設定されれば成功です。
  12. XAMPP をインストールしたフォルダを開き、 htdocs フォルダを開く。
  13. 右クリックし、 新規作成 から フォルダ を選択する。
  14. フォルダ名を php にする。
  15. エディタ(メモ帳) を起動し、下記のコードを入力する。
テストソースコード
<?php
phpinfo();
?>
  1. メニューから ファイル の 名前をつけて保存 をクリックする。
  2. xampp フォルダをクリックする。
  3. htdocs フォルダをクリックする。
  4. php フォルダをクリックする。
  5. ファイル名を index.php で保存する。
  6. http://localhost/php/　にアクセスし、phpのバージョンなどの情報が表示されれば成功です。
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2019-10-04T16:00:27+09:00

ExpressでさらっとAPI

まずは読み込み

const app = new (require('express'))()
const port = 3000
const bodyParser = require('body-parser')
const mysql = require('mysql')

dbの設定(localhost)

const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'ぱすわーど',
    database: 'でーたべーすめい'
});

dockerを使う場合は

host: 'localhost'

をDockerのコンテナに合わせて変える。

bodyPaserの設定

app.use(bodyParser.urlencoded({
    extended: true
}));
app.use(bodyParser.json());

hogeテーブルの一覧を表示

app.get('/hoge',(req,res) => {
    connection.query('select * from hoge_table', function (error, results, fields) {
        if (error) throw error;
        res.send(results);
    });
})

hogeテーブルにデータを追加

app.post('/hoge',(req,res) => {
    const fuga = req.body.fuga;
    connection.query('INSERT INTO hoge_table SET ?', { fuga: fuga }, (err, result) => {
        if (err) throw err;
        res.send(result);
    })
})

hogeのidで返す

app.get('/hoge/:id',(req,res) => {
    const id = req.params.id
    res.send({'id': id})
})

サーバーを呼ぶ

app.listen(port, error => {
    if (error) {
        console.error(error);
    } else {
        console.info('listen: ', port);
    }
});

オワオワリ

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

MariaDBの初期パスワード変更方法

動作環境

macOS Mojave バージョン10.14.6
mariadb ver15.1

はじめに

少し訳があってmacをクリーンインストールしまして、早速環境を入れ直そうと、mariadbをインストールしました。ところが、初期パスワード関連のエラーで随分手こずったので、その時の話をします。

問題点

mariadbをインストールし、とりあえず最低限の設定をしようと、mysql_secure_installationを実行しました。
初期rootパスワード(デフォルトではパスワードなしなのでEnterを押せば良いらしい、、、)を求められるのでとりあえずEnterを押しました。ところが、以下のようにエラーが出ました。

R1C36BB1156DC:~ heatflat$ mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none): 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

このエラーはrootへのアクセスができなかったということです。
この時点で私は、何らかの初期パスワードが勝手に設定されているのではないかと考えました。

解決手順

解決手順の概要としましては、パスワードを自分で設定し直すというものです。

1. とりあえず無理やりmysqlに入る

sudo mysql -u root -p

2. パスワードを変えるSQL文を実行する

use mysql;
ALTER USER 'root'@'localhost' IDENTIFIED BY "Password";
上記の2つ目のコマンドのPasswordの部分は自分の新しく設定するパスワードに変えてください。

以上で無事パスワードが設定され、rootユーザーとしてアクセスできるようになりました。

まとめ

今回の問題解決までに最も大変だったのは、解決手順2を見つけるまででした。
エラー文で検索をかけて情報を集めるうちに、解決手順1のsudoを使えば良いということまではわかったのですが、解決手順2の情報に至るまでに少し時間がかかりました。というのも、古いバージョンの情報ばかり検索にかかってしまったので、調べたコマンドは全て使えなかったからです。結局どのようにして正しい情報に行き着いたかというと、公式ドキュメントを読みました。読者の方の中には、上記の方法で解決できない方がいるかもしれません。それはもしかしたら、この記事の情報が古いものとなっらからかもしれません。そのため、一度該当するバージョンの公式ドキュメントを読んでみたら解決方法が見つかるかもしれません。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2019-10-04T09:33:07+09:00

webarenaでubuntu その54

概要

webarenaでubuntu18.04やってみた。
練習問題やってみた。

練習問題

いいねボタンを設置せよ。

写真

image.png

サンプルコード

<?php
    header("Access-Control-Allow-Origin: *");
    header("Content-Type:  application/json");
    $id = "test";
    $db = new PDO("mysql:host=localhost;dbname=mydb;", 'user', 'pass');
    if (!$db)
    {
        print "connect error!! <br>";
    }
    else
    {
        $sql = "SELECT * FROM counter WHERE id='$id'";
        $rs = $db->query($sql);
        if (!$rs)
        {
            print "Error in database!! 0 <br>";
            print $sql;
        }
        else
        {
            $userData = array();
            while ($row = $rs->fetch())
            {
                $userData[] = array('count' => $row['cnt']);
            }
            echo json_encode($userData);
        }
    }
?>

成果物

http://embed.plnkr.co/PKwLyPiQP2gHurD0i0pn/

以上。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2019-10-04T08:47:14+09:00

webarenaでubuntu その53

概要

webarenaでubuntu18.04やってみた。
練習問題やってみた。

練習問題

画像アクセスカウンターを表示、せよ。

サンプルコード

<?php
    $id = "test";
    $db = new PDO("mysql:host=localhost;dbname=mydb;", 'user', 'pass');
    if (!$db)
    {
        print "connect error!! <br>";
    }
    else
    {
        $sql = "UPDATE counter SET cnt = cnt + 1 WHERE id='$id'";
        $db->query($sql);
        $sql = "SELECT * FROM counter WHERE id='$id'";
        $rs = $db->query($sql);
        if (!$rs)
        {
            print "Error in database!! 0 <br>";
            print $sql;
        }
        else
        {
            $count = 0;
            while ($row = $rs->fetch())
            {
                $count = $row['cnt'];
            }
            header('Content-Type: image/gif');
            $im = imagecreatetruecolor(200, 50);
            $color = imagecolorallocate($im, 255, 255, 255);
            imagestring($im, 5, 10, 10, $count, $color);
            imagegif($im);
            imagedestroy($im);
        }
    }
?>

成果物

https://embed.plnkr.co/PITdnKLapRfpgIy4juzc/

以上。

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

DB系用語まとめ

目的

  • DBの勉強をしているときに理解できていない用語をまとめる。
    ※どんどん追記予定

リレーショナルデータベース(RDB)

  • 関連性によって管理されるデータベースのこと。
  • すべてのデータを表形式で表現する。

テーブル

  • DBのデータが入る表のこと。

カラム

  • テーブルの列のこと。

レコード

  • テーブルの行のこと。

フィールド

  • テーブル内の要素のこと。
  • テーブル内の一個のセルのこと。

SQL

  • Structured Query Languageの略である。
  • データベース言語のこと。

MySQL

  • Web系で主に使われているデータベースのこと。
  • オープンソースデータベースの一つである。
  • 使用しやすく、高速である。

クエリ

  • 命令のこと。
  • DB使用者が発行するもの。

トランザクション

  • クエリの集合体である。
  • 含まれる処理が完遂されたときのみ処理が確定される。
  • 含まれる処理が一つでも失敗したらすべての処理が取り消される。
  • データの整合性を保つための機能

データ型

  • データ自体の属性である。
  • 数値、文字列、バイナリ、日付、時間、などが存在する。

数値のデータ型

  • 数値の型の例
型 値の範囲 UNSIGNED ZEROFILL
TYNYINT -128～127 0～255 000～255
SMALLINT -32768～32767 0～65536 00000～65536
MEDIUMINT -8388608～8388607 0～16777215 00000000～16777215
INTEGER -2147483648～2147483647 0～4294967295 0000000000～4294967295
BIGINT -9223372036854775808～9223372036854775807 0～18446744073709551615 00000000000000000000～18446744073709551615

テキストのデータ型

  • 各バイト数を超えた場合はカットされる。
  • 可変長のものは格納された文字列に合わせて表現される。
  • 固定長のものは指定された文字列長さに、格納する文字列長さが満たなかった場合、満たない分だけ余白(文字としてのスペース)が格納される。
  • 文字列の型の例
型 バイト数
TYNYTEXT 0～255バイト（固定）
VARCHAR 0～255バイト（可変）
TEXT 0～65535バイト（固定）
MEDIUMTEXT 0～16777215バイト（固定）
LONGTEXT 0～4294967295バイト（固定）

日付・時間のデータ型

  • ダブルクオートかシングルクオートで囲んで格納する。
  • 日付・時間のデータ型の例
型 内容 範囲
DATETIME 日付と時刻 ’1000-01-01 00：00： 00’～’9999-12-31 23：59：59’
TIMESTAMP 日付と時刻 ’1970-01-01 00:00:01.000000’UTC～ ’2038-01-19 03:14:07.999999’
DATE 日付 ’1000-01-01’～’9999-12-31’
TIME 時刻 ’-838：59：59～838：59：59’
YEAR 時刻 ’1901～2155’

NULL値

  • データが存在しないことを表す値である。
  • 空文字や数値の0とは異なる。
  • 空文字や数値などのはデータとして前述のものが存在するためNULL値ではない。

SQL命令

  • DDL、DML、DCLの三種類が存在する。
  • DDL(Data Definition Language)はデータ定義言語でDBやテーブルを作成するときに使用する。
  • DML(Data Manipulation Language)はデータ操作言語でデータを操作するときに使用する。
  • DCL(DataControl Language)はデータ制御言語でトランザクション制御に使用する。

SQLの予約語

  • SQLの命令として定義されいている単語のこと。
  • 半角のアルファベットが使われる。
  • CREATEとかALTERとかがそう。

リテラル

  • SQL文中に書かれる具体的なデータのこと。
  • 数値リテラル、文字リテラル、日付リテラルなどがある。
  • 対応するデータ型が決まっている。
リテラル データ型
数値リテラル TINYINT,SMALLINT,MEDIUMINT,INTEGER,BIGINTほか
文字リテラル CHAR,VARCHAR,TEXTほか
日付リテラル DATE,DATETIME,TIMESTAMPほか

migrate(マイグレート)

  • データを別環境に移したりすること。
  • 新しい環境に切り替えたりすること。
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2019-10-04T01:03:11+09:00

MyBatisでまとめてinsertする方法

概要

  • Listのデータをinsertしたいときに1件ずつinsertするのは無駄なのでデータをまとめて一括でinsertしたい
  • MyBatisGeneratorで自動生成されたMapperをカスタマイズした

注意事項

  • 再びgenerateしたら上書きされて消えるので要注意
  • IDがAUTO INCREMENTの場合はやり方が違うらしい

環境

  • Java8
    • SpringBoot v2.0.6.RELEASE
    • mybatis-spring-boot-starter v1.3.2
  • MySQL v5.7.25

方法

やることは2つ。
1. Mapperクラスに一括insertメソッドを定義する
2. xmlに一括insertの設定を追加する

例

例えばこんなテーブル(MySQL)があったとする。

user_friend.sql
CREATE TABLE user_friend (
  user_id int(10) NOT NULL,
  friend_id int(10) NOT NULL
  friend_name varchar(30) NOT NULL,
  created_at datetime NOT NULL,
  updated_at datetime NOT NULL,
  PRIMARY KEY (user_id, friend_id)
);

Mapperクラスにメソッドを追加する

  • insertBulkのメソッドを追加する
    • 引数にinsertしたいデータのListを渡す
    • @Paramで名前を付けるかどうかは任意
UserFriendMapper.java
int insertBulk(@Param("friendList")List<UserFriend> friendList);

xmlに設定を追加する

  • UserFriendMapper.xmlにinsert id="insertBulk"を追加する
    • parameterType="java.util.List"に指定する
    • Mapperで@Paramを指定しなかった場合はcollection="list"と指定する
UserFriendMapper.xml
<!-- これを追加する -->
<insert id="insertBulk" parameterType="java.util.List">
    insert into user_friend
      (user_id, friend_id, friend_name, created_at, updated_at)
    values
    <foreach collection="friendList" item="fr" separator=","> 
    (
      #{fr.userId,jdbcType=INTEGER}, #{fr.friendId,jdbcType=INTEGER},
      #{fr.friendName,jdbcType=VARCHAR}, #{fr.createdAt,jdbcType=TIMESTAMP},
      #{fr.updatedAt,jdbcType=TIMESTAMP}
    )
    </foreach>
</insert>

Java(SpringBoot)側での使い方

Mapperクラスに追加したメソッドを呼び出すだけ。

FriendService.java
@Service
public class FriendService {

    @Autowired
    private UserFriendMapper userFriendMapper;

    /**
     * @return int insertした件数
     */
    public int insertFriends(int userId, List<UserFriend> saveFriends) {

        return userFriendMapper.insertBulk(saveFriends);
    }
}

参考

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

日付別

タグ一覧