Home > MySQL

MySQL Archive

Debian GNU/Linux etch に Tritonn をインストールしなおしてみたよ

先日、Debian GNU/Linux に Tritonn をインストールしてみたよやDebian GNU/Linux etch の PHP で Tritonn に接続するで Tritonn のインストールをお伝えしてきましたが、どうにも PHP がセグメンテーション違反を起こし、それがもうどうにもさっぱりなので気を取り直して Tritonn のインストール場所を Debian のパッケージにあわせてインストールしなおしてみました。

$ wget http://jaist.dl.sourceforge.jp/tritonn/28541/mysql-5.0.51-tritonn-1.0.8-linux-i686.tar.gz
$ tar zxvf mysql-5.0.51-tritonn-1.0.8-linux-i686.tar.gz
$ sudo mv mysql-5.0.51-tritonn-1.0.8-linux-i686 /var/lib/mysql
$ sudo groupadd mysql
$ sudo useradd -g mysql mysql
$ cd /var/lib/mysql
$ sudo chown -R root:root .
$ sudo chown -R mysql:root data
$ sudo scripts/mysql_install_db -umysql
$ sudo cp support-files/mysql.server /etc/init.d/mysql
$ sudo cp /var/lib/mysql/support-files/my-small.cnf /etc/my.cnf
$ sudo vim /etc/my.cnf

[client]
socket = /var/run/mysqld/mysqld.sock
[mysqld]
socket = /var/run/mysqld/mysqld.sock
basedir = /var/lib/mysql

$ sudo mkdir /var/run/mysqld
$ sudo chown mysql /var/run/mysqld

$ sudo update-rc.d mysql defaults
$ sudo ln -s /usr/include/mysql /var/lib/mysql/include
$ sudo cp -r /var/lib/mysql/lib/* /usr/lib

こんな感じで Apache と MySQL を再起動すると正常に動作しました。

ためしに「郵便番号と住所の対応表を「郵便番号データダウンロード – 日本郵便」からダウンロードして以下の様なテーブルを作って、そこに郵便データを登録するスクリプト」を作ってデータベースにガシガシ登録して全文検索を試してみました。

CREATE TABLE `zipcode` (
  `zipcode` varchar(7) default NULL,
  `prefecture_kana` varchar(36) default NULL,
  `city_kana` varchar(255) default NULL,
  `town_kana` varchar(255) default NULL,
  `prefecture` varchar(24) default NULL,
  `city` varchar(255) default NULL,
  `town` varchar(255) default NULL,
  FULLTEXT INDEX USING NGRAM, SECTIONALIZE (`zipcode`,`prefecture_kana`,`city_kana`,`town_kana`,`prefecture`,`city`,`town`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

カラムはそれぞれ

zipcode
郵便番号
prefecture_kana
都道府県（ひらがな）
city_kana
市区町村（ひらがな）
town_kana
町域
prefecture
都道府県
city
市区町村
town
町域

と言う感じです。

ためしに以下の以下のスクリプトを実行してみると

<?php
$DB = new PDO('mysql:host=localhost;dbname=test_db', 'test_user', '******');
$q = $DB->query(
    "SELECT * FROM `zipcode` WHERE MATCH(`zipcode`, `prefecture_kana`, ".
    "`city_kana`, `town_kana`, `prefecture`, `city`, `town`) ".
    "AGAINST('*W1, 2, 3, 4, 5, 6, 7 かわさき' IN BOOLEAN MODE) LIMIT 0, 10");
$r = $q->fetchAll(PDO::FETCH_ASSOC);
var_dump($r);
?>

きちんと結果が返ってきました。

array(10) {
  [0]=>
  array(7) {
    ["zipcode"]=>
    string(7) "0280051"
    ["prefecture_kana"]=>
    string(15) "いわてけん"
    ["city_kana"]=>
    string(9) "くじし"
    ["town_kana"]=>
    string(21) "かわさきちょう"
    ["prefecture"]=>
    string(9) "岩手県"
    ["city"]=>
    string(9) "久慈市"
    ["town"]=>
    string(9) "川崎町"
  }
  [1]=>
  array(7) {
    ["zipcode"]=>
    string(7) "9391553"
    ["prefecture_kana"]=>
    string(15) "とやまけん"
    ["city_kana"]=>
    string(12) "なんとし"
    ["town_kana"]=>
    string(18) "かみかわさき"
    ["prefecture"]=>
    string(9) "富山県"
    ["city"]=>
    string(9) "南砺市"
    ["town"]=>
    string(9) "上川崎"
  }
  [2]=>
  array(7) {
    ["zipcode"]=>
    string(7) "9130025"
    ["prefecture_kana"]=>
    string(15) "ふくいけん"
    ["city_kana"]=>
    string(12) "さかいし"
    ["town_kana"]=>
    string(30) "みくにちょうかわさき"
    ["prefecture"]=>
    string(9) "福井県"
    ["city"]=>
    string(9) "坂井市"
    ["town"]=>
    string(15) "三国町川崎"
  }
  [3]=>
  array(7) {
    ["zipcode"]=>
    string(7) "8720731"
    ["prefecture_kana"]=>
    string(18) "おおいたけん"
    ["city_kana"]=>
    string(9) "うさし"
    ["town_kana"]=>
    string(27) "あじむまちかわさき"
    ["prefecture"]=>
    string(9) "大分県"
    ["city"]=>
    string(9) "宇佐市"
    ["town"]=>
    string(18) "安心院町川崎"
  }
  [4]=>
  array(7) {
    ["zipcode"]=>
    string(7) "9690401"
    ["prefecture_kana"]=>
    string(18) "ふくしまけん"
    ["city_kana"]=>
    string(36) "いわせぐんかがみいしまち"
    ["town_kana"]=>
    string(18) "かわさきまち"
    ["prefecture"]=>
    string(9) "福島県"
    ["city"]=>
    string(18) "岩瀬郡鏡石町"
    ["town"]=>
    string(9) "川崎町"
  }
  [5]=>
  array(7) {
    ["zipcode"]=>
    string(7) "9170081"
    ["prefecture_kana"]=>
    string(15) "ふくいけん"
    ["city_kana"]=>
    string(12) "おばまし"
    ["town_kana"]=>
    string(12) "かわさき"
    ["prefecture"]=>
    string(9) "福井県"
    ["city"]=>
    string(9) "小浜市"
    ["town"]=>
    string(6) "川崎"
  }
  [6]=>
  array(7) {
    ["zipcode"]=>
    string(7) "2050021"
    ["prefecture_kana"]=>
    string(18) "とうきょうと"
    ["city_kana"]=>
    string(12) "はむらし"
    ["town_kana"]=>
    string(12) "かわさき"
    ["prefecture"]=>
    string(9) "東京都"
    ["city"]=>
    string(9) "羽村市"
    ["town"]=>
    string(6) "川崎"
  }
  [7]=>
  array(7) {
    ["zipcode"]=>
    string(7) "2891331"
    ["prefecture_kana"]=>
    string(12) "ちばけん"
    ["city_kana"]=>
    string(12) "さんむし"
    ["town_kana"]=>
    string(12) "かわさき"
    ["prefecture"]=>
    string(9) "千葉県"
    ["city"]=>
    string(9) "山武市"
    ["town"]=>
    string(6) "川崎"
  }
  [8]=>
  array(7) {
    ["zipcode"]=>
    string(7) "8270000"
    ["prefecture_kana"]=>
    string(18) "ふくおかけん"
    ["city_kana"]=>
    string(33) "たがわぐんかわさきまち"
    ["town_kana"]=>
    string(0) ""
    ["prefecture"]=>
    string(9) "福岡県"
    ["city"]=>
    string(18) "田川郡川崎町"
    ["town"]=>
    string(0) ""
  }
  [9]=>
  array(7) {
    ["zipcode"]=>
    string(7) "8270001"
    ["prefecture_kana"]=>
    string(18) "ふくおかけん"
    ["city_kana"]=>
    string(33) "たがわぐんかわさきまち"
    ["town_kana"]=>
    string(9) "あまぎ"
    ["prefecture"]=>
    string(9) "福岡県"
    ["city"]=>
    string(18) "田川郡川崎町"
    ["town"]=>
    string(9) "安眞木"
  }
}

Senna はよく知らないのでこれからお勉強です。

携帯電話のIPアドレス範囲テーブル

このエントリは[http://spais.jp/output/mobileip]に移動しました。

携帯電話各キャリア（au, DoCoMo, SoftBank WILLCOM）で利用している携帯電話用のIPアドレス範囲を DB 化しました。
MySQL でテーブルをダンプしたやつですが、よろしければお使い下さい。
zip 圧縮したクエリ
up したクエリは MySQL 用ですがクエリを生成したスクリプトは下記のものですので、他のデータベースで利用される方は下記のスクリプトをちょちょっと書き換えてお使いいただいても結構です。
※データベースの接続には PEAR::MDB2 を利用してますので、そこらへんも普段ご利用のアレに置き換えていただければと思います。

<?php
$ip = array(
    'au' => array(
        '210.169.40.0/24',    '210.196.3.192/26',   '210.196.5.192/26',
        '210.230.128.0/24',   '210.230.141.192/26', '210.234.105.32/29',
        '210.234.108.64/26',  '210.251.1.192/26',   '210.251.2.0/27',
        '211.5.1.0/24',       '211.5.2.128/25',     '211.5.7.0/24',
        '218.222.1.0/24',     '61.117.0.0/24',      '61.117.1.0/24',
        '61.117.2.0/26',      '61.202.3.0/24',      '219.108.158.0/26',
        '219.125.148.0/24',   '222.5.63.0/24',      '222.7.56.0/24',
        '222.5.62.128/25',    '222.7.57.0/24',      '59.135.38.128/25',
        '219.108.157.0/25',   '219.125.151.128/25', '219.125.145.0/25',
        '121.111.231.0/25'
    ),
    'docomo' => array(
        '210.153.84.0/24',    '210.136.161.0/24',   '210.153.86.0/24'
    ),
    'softbank' => array(
        '202.179.204.0/24',   '202.253.96.248/29',  '210.146.7.192/26',
        '210.146.60.192/26',  '210.151.9.128/26',   '210.169.130.112/29',
        '210.169.130.120/29', '210.169.176.0/24',   '210.175.1.128/25',
        '210.228.189.0/24',   '211.8.159.128/25'
    ),
    'willcom' => array(
        '61.198.142.0/24',    '219.108.14.0/24',    '61.198.161.0/24',
        '219.108.0.0/24',     '61.198.249.0/24',    '219.108.1.0/24',
        '61.198.250.0/24',    '219.108.2.0/24',     '61.198.253.0/24',
        '219.108.3.0/24',     '61.198.254.0/24',    '219.108.4.0/24',
        '61.198.255.0/24',    '219.108.5.0/24',     '61.204.3.0/25',
        '219.108.6.0/24',     '61.204.4.0/24',      '221.119.0.0/24',
        '61.204.6.0/25',      '221.119.1.0/24',     '125.28.4.0/24',
        '221.119.2.0/24',     '125.28.5.0/24',      '221.119.3.0/24',
        '125.28.6.0/24',      '221.119.4.0/24',     '125.28.7.0/24',
        '221.119.5.0/24',     '125.28.8.0/24',      '221.119.6.0/24',
        '211.18.235.0/24',    '221.119.7.0/24',     '211.18.238.0/24',
        '221.119.8.0/24',     '211.18.239.0/24',    '221.119.9.0/24',
        '125.28.11.0/24',     '125.28.13.0/24',     '125.28.12.0/24',
        '125.28.14.0/24',     '125.28.2.0/24',      '125.28.3.0/24',
        '211.18.232.0/24',    '211.18.233.0/24',    '211.18.236.0/24',
        '211.18.237.0/24',    '125.28.0.0/24',      '125.28.1.0/24',
        '61.204.0.0/24',      '210.168.246.0/24',   '210.168.247.0/24',
        '219.108.7.0/24',     '61.204.2.0/24',      '61.204.5.0/24',
        '61.198.129.0/24',    '61.198.140.0/24',    '61.198.141.0/24',
        '125.28.15.0/24',     '61.198.165.0/24',    '61.198.166.0/24',
        '61.198.168.0/24',    '61.198.169.0/24',    '61.198.170.0/24',
        '61.198.248.0/24',    '125.28.16.0/24',     '125.28.17.0/24',
        '211.18.234.0/24',    '219.108.8.0/24',     '219.108.9.0/24',
        '219.108.10.0/24',    '61.198.138.100/32',  '61.198.138.101/32',
        '61.198.138.102/32',  '61.198.139.160/28',  '61.198.139.128/27',
        '61.198.138.103/32',  '61.198.139.0/29',    '219.108.15.0/24'
    )
);
//$DB = PEAR::MDB2
$pattern = '/^([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\/([0-9]{1,2})$/';
foreach($ip as $career => $hosts){
    foreach($hosts as $iprange){
        if(preg_match($pattern, $iprange, $matchs)){
            $range = pow(2, (32 - $matchs[5]));
            for($i = 0; $i <$range; $i++){
                $decIP = ((($matchs[1] * 256 + $matchs[2]) * 256 + $matchs[3]) * 256 + $matchs[4]) + $i;
                $ipaddress = str_pad(decbin($decIP), 32, 0, STR_PAD_LEFT);
                $ipaddress = bindec(substr($ipaddress,  0, 8)).'.'.
                             bindec(substr($ipaddress,  8, 8)).'.'.
                             bindec(substr($ipaddress, 16, 8)).'.'.
                             bindec(substr($ipaddress, 24, 8));
                $values[] = "('$decIP', '$career', '$ipaddress')";
            }
        }
        if(isset($values)){
            $r = $DB->exec("INSERT `mobileip` (`decip`, `career`, `ipaddress`) VALUES ".implode(', ',$values));
            if(PEAR::isError($r)) die($r->getMessage());
            unset($values);
        }
    }
}

各キャリアのIPアドレス範囲について

各キャリアのIPアドレス範囲のデータソースは以下のとおりです。

使い方

使い方なんてのはそりゃあもう人それぞれかと思いますが、僕は認証システムに組み込んでおります。
が、これだけで決定するわけではなく、もちろん環境変数との整合性を以って確認をするようにしております。
認証システムに組み込むことによってPCでの認証と同じレイヤーで携帯電話の固体識別番号での認証が行えたりします。

そのほか

decip カラムは IP アドレスを10進数に変換した数値です。
MySQL であれば

SELECT `career` FROM `mobileip` WHERE `ipaddress` = '$ipaddress'

とかでキャリアが返ってきますので、環境変数との整合性を確認するキーとでもして下さい。

各キャリアの IP アドレス範囲は2007年9月25日現在のデータです。
もちろん各キャリアごと予告なくいきなり IP アドレス範囲が変わります。
各キャリアとも一般公開向けに IP アドレス範囲をデータ配信などというフレンドリーな窓口はございませんので、常に気を配って下さい。
また、up したクエリやスクリプトを使用して発生した障害や損害などいかなる問題についても僕は責任を取れませんので、ご利用の際は自己責任でお使い下さい。

2007/10/26追記

コメントでご指摘いただき、下記のようにスクリプトを修正いたしました。
Gyokimae さんありがとうございます！
無駄に処理を増やさない！PHP のお兄さんとのお約束を忘れていました！

<?php
$ip = array(
    'au' => array(
        '210.169.40.0/24',    '210.196.3.192/26',   '210.196.5.192/26',
        '210.230.128.0/24',   '210.230.141.192/26', '210.234.105.32/29',
        '210.234.108.64/26',  '210.251.1.192/26',   '210.251.2.0/27',
        '211.5.1.0/24',       '211.5.2.128/25',     '211.5.7.0/24',
        '218.222.1.0/24',     '61.117.0.0/24',      '61.117.1.0/24',
        '61.117.2.0/26',      '61.202.3.0/24',      '219.108.158.0/26',
        '219.125.148.0/24',   '222.5.63.0/24',      '222.7.56.0/24',
        '222.5.62.128/25',    '222.7.57.0/24',      '59.135.38.128/25',
        '219.108.157.0/25',   '219.125.151.128/25', '219.125.145.0/25',
        '121.111.231.0/25'
    ),
    'docomo' => array(
        '210.153.84.0/24',    '210.136.161.0/24',   '210.153.86.0/24'
    ),
    'softbank' => array(
        '202.179.204.0/24',   '202.253.96.248/29',  '210.146.7.192/26',
        '210.146.60.192/26',  '210.151.9.128/26',   '210.169.130.112/29',
        '210.169.130.120/29', '210.169.176.0/24',   '210.175.1.128/25',
        '210.228.189.0/24',   '211.8.159.128/25'
    ),
    'willcom' => array(
        '61.198.142.0/24',    '219.108.14.0/24',    '61.198.161.0/24',
        '219.108.0.0/24',     '61.198.249.0/24',    '219.108.1.0/24',
        '61.198.250.0/24',    '219.108.2.0/24',     '61.198.253.0/24',
        '219.108.3.0/24',     '61.198.254.0/24',    '219.108.4.0/24',
        '61.198.255.0/24',    '219.108.5.0/24',     '61.204.3.0/25',
        '219.108.6.0/24',     '61.204.4.0/24',      '221.119.0.0/24',
        '61.204.6.0/25',      '221.119.1.0/24',     '125.28.4.0/24',
        '221.119.2.0/24',     '125.28.5.0/24',      '221.119.3.0/24',
        '125.28.6.0/24',      '221.119.4.0/24',     '125.28.7.0/24',
        '221.119.5.0/24',     '125.28.8.0/24',      '221.119.6.0/24',
        '211.18.235.0/24',    '221.119.7.0/24',     '211.18.238.0/24',
        '221.119.8.0/24',     '211.18.239.0/24',    '221.119.9.0/24',
        '125.28.11.0/24',     '125.28.13.0/24',     '125.28.12.0/24',
        '125.28.14.0/24',     '125.28.2.0/24',      '125.28.3.0/24',
        '211.18.232.0/24',    '211.18.233.0/24',    '211.18.236.0/24',
        '211.18.237.0/24',    '125.28.0.0/24',      '125.28.1.0/24',
        '61.204.0.0/24',      '210.168.246.0/24',   '210.168.247.0/24',
        '219.108.7.0/24',     '61.204.2.0/24',      '61.204.5.0/24',
        '61.198.129.0/24',    '61.198.140.0/24',    '61.198.141.0/24',
        '125.28.15.0/24',     '61.198.165.0/24',    '61.198.166.0/24',
        '61.198.168.0/24',    '61.198.169.0/24',    '61.198.170.0/24',
        '61.198.248.0/24',    '125.28.16.0/24',     '125.28.17.0/24',
        '211.18.234.0/24',    '219.108.8.0/24',     '219.108.9.0/24',
        '219.108.10.0/24',    '61.198.138.100/32',  '61.198.138.101/32',
        '61.198.138.102/32',  '61.198.139.160/28',  '61.198.139.128/27',
        '61.198.138.103/32',  '61.198.139.0/29',    '219.108.15.0/24'
    )
);
//$DB = PEAR::MDB2
$pattern = '/^([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\/([0-9]{1,2})$/';
foreach($ip as $career => $hosts){
    foreach($hosts as $iprange){
        if(preg_match($pattern, $iprange, $matchs)){
            $range = pow(2, (32 - $matchs[5]));
            for($decIP = ((($matchs[1] * 256 + $matchs[2]) * 256 + $matchs[3]) * 256 + $matchs[4]), $decMaxIP = $decIP + $range ; $decIP <$decMaxIP; $decIP++){
                $ipaddress = str_pad(decbin($decIP), 32, 0, STR_PAD_LEFT);
                $ipaddress = bindec(substr($ipaddress,  0, 8)).'.'.
                             bindec(substr($ipaddress,  8, 8)).'.'.
                             bindec(substr($ipaddress, 16, 8)).'.'.
                             bindec(substr($ipaddress, 24, 8));
                $values[] = "('$decIP', '$career', '$ipaddress')";
            }
        }
        if(isset($values)){
            $r = $DB->exec("INSERT `mobileip` (`decip`, `career`, `ipaddress`) VALUES ".implode(', ',$values));
            if(PEAR::isError($r)) die($r->getMessage());
            unset($values);
        }
    }
}

”この中にはありません”という判断

MySQL で例えば account テーブルと order テーブルがあり、order テーブルのすべてのレコードの type カラムのうちどれかと account テーブルの color カラムの値が同じだったら。という判断は ANY を使います。

SELECT `id` FROM `account` WHERE `color` = ANY (SELECT `type` FROM `order` WHERE 1);

こんな感じ。要するに「サブクエリの結果セットのうちどれかひとつ以上と等価」という条件なのですが、これを逆にする「サブクエリの結果セットのどれとも等価であってはならない」という条件を持たせる場合下記のようなクエリを投げると

SELECT `id` FROM `account` WHERE `color` != ANY (SELECT `type` FROM `order` WHERE 1);

失敗します。
これだと「サブクエリの結果セットのうちどれかひとつ以上と等価ではない」という条件になってしまいますよね。
正しくは

SELECT `id` FROM `account` WHERE (`color` = ANY (SELECT `type` FROM `order` WHERE 1)) != 1;

こうですね。

要するにちゃんと考えれば分かる事なのにとちった自分への戒めという意味です。

MySQL でビットの比較

例えば30個の Boolean 値をテーブルに格納したいときに30カラム用意するのではなく、1カラムに10010101…のようにビットを立てて保存するようにしています。僕は。
他のレコードとの論理的な比較を行う場合、MySQL では SELECT BINARY 1101 XOR 1111 などとしても0010ではなく0が返ってきます。これはまずい。まずいって言うかそれが普通っぽい。
そんなわけで面倒ですが1文字ずつ比較しています。

例えば account テーブルには id, username, bits のカラムがあったとします。bits には CHAR(10) で id, username の値はユニークだったとして
username が nic のレコードとひとつでも同じビットが1なレコードを抽出したい場合こんなクエリを投げています。

SELECT
 `id`
FROM
 `account`
WHERE
  SUBSTRING(`bits`,0,1) = SUBSTRING(@i:=(SELECT `bits` FROM `account` WHERE `username` = 'nic'),0,1) OR
  SUBSTRING(`bits`,1,1) = SUBSTRING(@i,1,1) OR
  SUBSTRING(`bits`,2,1) = SUBSTRING(@i,2,1) OR
  SUBSTRING(`bits`,3,1) = SUBSTRING(@i,3,1) OR
  SUBSTRING(`bits`,4,1) = SUBSTRING(@i,4,1) OR
  SUBSTRING(`bits`,5,1) = SUBSTRING(@i,5,1) OR
  SUBSTRING(`bits`,6,1) = SUBSTRING(@i,6,1) OR
  SUBSTRING(`bits`,7,1) = SUBSTRING(@i,7,1) OR
  SUBSTRING(`bits`,8,1) = SUBSTRING(@i,8,1) OR
  SUBSTRING(`bits`,9,1) = SUBSTRING(@i,9,1);

・・・これは方法として有用なのかどうなのかいまいちわかりかねるのですが、今のところ僕が思いつくベストプラクティスです。

もし、識者の方々の中に「いや、こうした方が」や「そもそもそうじゃなくて」と言ったご意見をお持ちの方がいらっしゃいましたら、コメントしていただければ幸いです。

重複した値をもつレコードだけを抽出

例えば host カラムの値が重複しているレコードを抽出するには

SELECT　`username`　FROM　`account`　WHERE
(SELECT　COUNT(`id`) FROM　`account` AS `ref`　WHERE　`ref`.`host` = `account`.`host`) > 1
ORDER BY `host`;

こんな感じ。
ただし host カラムがインデックスじゃないとやばげ。

ホーム > MySQL

Pages
Recent Entries
Categories
Archives
Tag Cloud
Search
Feeds
Meta

Return to page top