mysql テーブルサイズ取得
Posted in DB on 8月 29th, 2010 by Site Administratorshow table status from DB名 like ‘table名’;
Data_lengthがテーブルサイズ
show table status from DB名 like ‘table名’;
Data_lengthがテーブルサイズ
アソシエーション設定をして2階層離れたデータもまとめて取得する場合、JOINして取得されるのは1階層離れたデータまでなんですね。
たとえば下記の例で「生産者のrank=Bの商品を購入した履歴を出力する」という場合、条件で「Producer.rank=’B’」を指定するわけですが、
「1.belongsTo」にて条件を指定するとエラーになります。
逆にそれ以外の場合は、「ユーザー1が購入した商品の履歴を出力する」等で条件で「History.user_id=1」を指定するとエラーとなります。
使い分けが悩ましいところです。
——————————————————————————————————-
3つのテーブルがあります。定義、データは下記のとおりです。
histories(購入履歴)
id | int |
fruit_id | int |
user_id | int |
id | fruit_id | user_id |
1 | 1 | 1 |
2 | 2 | 1 |
fruits(商品)
id | int |
producer_id | int |
id | producer_id |
1 | 1 |
2 | 2 |
Producers(生産者)
id | int |
rank | varchar |
id | rank |
1 | A |
2 | B |
modelにアソシエーション設定をして多階層のデータをまとめて取得する場合の挙動はそれぞれ次のようになります。
1.belongsTo
History:$belongsTo=’Fruit’;
Fruit:$belongsTo=’Producer’;
$this->History->recursive = 2;
$this->History->find(‘all’);
発行されるSQL:
・SELECT `History`.`id`, `History`.`fruit_id`, `History`.`user_id`, `Fruit`.`id`, `Fruit`.`producer_id` FROM `histories` AS `History` LEFT JOIN `fruits` AS `Fruit` ON (`History`.`fruit_id` = `Fruit`.`id`) WHERE 1 = 1
・SELECT `Fruit`.`id`, `Fruit`.`producer_id` FROM `fruits` AS `Fruit` WHERE `Fruit`.`id` = 1
・SELECT `Producer`.`id`, `Producer`.`rank` FROM `producers` AS `Producer` WHERE `Producer`.`id` = 1
・SELECT `Fruit`.`id`, `Fruit`.`producer_id` FROM `fruits` AS `Fruit` WHERE `Fruit`.`id` = 2
・SELECT `Producer`.`id`, `Producer`.`rank` FROM `producers` AS `Producer` WHERE `Producer`.`id` = 2
取得データ:
Array(
[0]=>Array
(
[History]=>Array(
[id]=>1
[fruit_id]=>1
[user_id]=>1)
[Fruit]=>Array(
[id]=>1
[producer_id]=>1
[Producer]=>Array(
[id]=>1
[rank]=>A)))
[1]=>Array(
[History]=>Array(
[id]=>2
[fruit_id]=>2
[user_id]=>1)
[Fruit]=>Array(
[id]=>2
[producer_id]=>2
[Producer]=>Array(
[id]=>2
[rank]=>B))))
2.hasMany
Producer:$hasMany=’Fruit’;
Fruit:$hasMany=’History’;
$this->Producer->recursive = 2;
$this->Producer->find(‘all’);
発行されるSQL:
・SELECT `Producer`.`id`, `Producer`.`rank` FROM `producers` AS `Producer` WHERE 1 = 1
・SELECT `Fruit`.`id`, `Fruit`.`producer_id` FROM `fruits` AS `Fruit` WHERE `Fruit`.`producer_id` IN (1, 2)
・SELECT `History`.`id`, `History`.`fruit_id`, `History`.`user_id` FROM `histories` AS `History` WHERE `History`.`fruit_id` IN (1, 2)
取得データ:
Array(
[0] => Array(
[Producer] => Array(
[id] => 1
[rank] => A)
[Fruit] => Array(
[0] => Array(
[id] => 1
[producer_id] => 1
[History] => Array(
[0] => Array(
[id] => 1
[fruit_id] => 1
[user_id] => 1)))))
[1] => Array(
[Producer] => Array(
[id] => 2
[rank] => B)
[Fruit] => Array(
[0] => Array(
[id] => 2
[producer_id] => 2
[History] => Array(
[0] => Array(
[id] => 2
[fruit_id] => 2
[user_id] => 1))))))
3.belongsTo、hasMany混合
Fruit:
$belongsTo=’Producer’;
$hasMany=’History’;
$this->Fruit->recursive = 2;
$this->Fruit->find(‘all’);
発行されるSQL:
・SELECT `Fruit`.`id`, `Fruit`.`producer_id`, `Producer`.`id`, `Producer`.`rank` FROM `fruits` AS `Fruit` LEFT JOIN `producers` AS `Producer` ON (`Fruit`.`producer_id` = `Producer`.`id`) WHERE 1 = 1
・SELECT `Producer`.`id`, `Producer`.`rank` FROM `producers` AS `Producer` WHERE `Producer`.`id` = 1
・SELECT `Producer`.`id`, `Producer`.`rank` FROM `producers` AS `Producer` WHERE `Producer`.`id` = 2
・SELECT `History`.`id`, `History`.`fruit_id`, `History`.`user_id` FROM `histories` AS `History` WHERE `History`.`fruit_id` IN (1, 2)
取得データ:
Array(
[0] => Array(
[Fruit] => Array(
[id] => 1
[producer_id] => 1)
[Producer] => Array(
[id] => 1
[rank] => A)
[History] => Array(
[0] => Array(
[id] => 1
[fruit_id] => 1
[user_id] => 1)))
[1] => Array(
[Fruit] => Array(
[id] => 2
[producer_id] => 2)
[Producer] => Array(
[id] => 2
[rank] => B)
[History] => Array(
[0] => Array(
[id] => 2
[fruit_id] => 2
[user_id] => 1))))
1.ペアリング(パスワードは「0000」)
2.設定→接続→BluetoothでCOMポート設定。M241のCOMポートをCOM6に設定。
3.設定→システム→外部GPSでハードウェアをCOM6、プログラムをCOM2に設定。