mysql create table 索引のインデックスが長すぎます

Specified key was too long; max key length is 767 bytes


	mail varchar(255) NOT NULL,
	 kibobi date NOT NULL,
  	KEY kibobi (kibobi(191)),
   UNIQUE KEY mail (mail(191))

とか長さを指定すればOK

日本語エンコードがutf8の場合はバイト数が少ないのでエラーは出ないが
utf8mb4にすると大きくなるので警告が出るようになった。

bindValueでループしてSQL文作成

  $sql = 'DELETE FROM tablename WHERE ';
  $j=count($_POST['chk']);
    for ($i=0; $i < $j; $i++) { if ($i === 0 ) { $sql .= ' id=? '; }else{ $sql .= ' OR id=? '; } } $sth = $dbh -> prepare($sql);
  
    $i=1;
    foreach ($_POST['chk'] as $chk) {
      $id = (int)$chk;
      $sth -> bindValue($i, $id, PDO::PARAM_INT);
      $i++;
    }
   
    $sth -> execute(); 
 

SQLのexecute()をループの中に書くべきではない。
複数のバインドを行ってから一回のexecuteを実行するべき

 


INSERTの場合

$colum_name = array("sei","mei","sei_kn","mei_kn","ybn","pref","city","addr","tel","mail");
$key_array = array('姓','名','セイ','メイ','〒','都道府県','市町村','以降の住所','TEL','メール');
$cname='';$cvalues='';
foreach ($colum_name as $v) {
$cname .= $v .',';
$cvalues .= ':' . $v .',' ;
}
$cname= rtrim($cname,',') ;
$cvalues = rtrim($cvalues,',');
//$sql="INSERT INTO kokyak( $cname ) VALUES (?,?,?,?,?,?,?,?,?,?) ";
$sql="INSERT INTO kokyak( $cname ) VALUES ( $cvalues ) ";
echo $sql .PHP_EOL;
$sth = $dbh->prepare($sql);
$dbh->beginTransaction();//トランザクション処理を開始 
try {
// bindValueは値をバインドします。
// bindParamは変数をバインドします。評価は実行時です。
foreach ($colum_name as $k => $v) {
$sth->bindValue(":$v", "{$post_hash[$key_array[$k]]}", PDO::PARAM_STR);

}
$sth->execute();

MySQL rootのパスワードを忘れてしまった場合の対処方法

Dosプロンプトでディレクトリを変更

MySQLのrootのパスワードを忘れてしまった場合でも、以下の
対処をすることでパスワードの変更を行うことが出来ます。

(1)MySQLサービスの停止
●Windows
「コントロールパネル」→「管理コンソール」→「サービス」の画面を起動して、
「MySQL」サービスを停止してください。
●LINUX
以下のコマンドを実行して、デーモンを停止してください。
#/etc/init.d/mysql stop

(2)パスワード無しでログイン出来るようにMySQLを起動
●Windows
コマンドプロンプトを起動して以下のコマンドを実行してください。
mysqld-nt --skip-grant-tables
※応答待ちで止まりますが、そのままで問題ありません。

●LINUX
以下のコマンドを実行して、MySQLを起動してください。
#mysqld_safe --skip-grant-tables

(3)MySQLに接続して、パスワード変更
●Windows、LINUX共通
コマンドプロンプトをもう1つ起動させ、以下のコマンドでMySQLに接続、
及びパスワード変更コマンドを実行してください。
以下の画面はWindowsの物ですが、Linuxの場合も同じコマンドで変更できます。
なお、下記では新しいパスワードを「pass」で設定しています。
C:\Users\sasuke>mysql -u root mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.86-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>update user set password=PASSWORD('pass') where user='root' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql>FLUSH PRIVILEGES;

(4)パソコンの再起動
パソコンの再起動、又はmysqlのプロセスを停止後にMySQLのサービスを起動しましょう。
上記で設定した新しいパスワードでログイン出来るようになります。

参考
http://sasuke.main.jp/mysqlrootpass.html

Amazon在庫 出品管理

amazonで無在庫販売を行うためのツールです。
国内のamazonと海外のamazonから取得したデータを取り込んで、 送料や手数料・利益率などを含んだ売価を計算します。
データ件数は数万件に対応しています、為替レートや利益率の調整、安すぎる売価の一括調整 と出品データの書出しができます。

このシステムにはamazonから商品データを取得する機能はありませんが、オプションで 設置することも可能です。
Amazon在庫 出品管理ツール

myslq 年齢計算 関数 文字連結

年月日が別々に記録されているフィールドをゼロづめにして、
連結して、
日付にキャストして、
今日の日付から引き算して、
年齢にする方法

SELECT
(YEAR(CURDATE()) - k.bthY) - (RIGHT(CURDATE(), 5) < RIGHT(cast(concat(k.bthY,lpad(k.bthM,2,'0'),lpad(k.bthD,2,'0')) as date), 5)) as '年齢'
FLOM table

一回のクエリで値の有無を探して、あれば見出し行も書き出し、無ければないで

検索した結果、戻り値がないなら[ありませんでした]
あったら、内容を返す。

これを一回のクエリで実行したい。

$strsql="SELECT なんだ*, かんだ
FROM このテーブル
LEFT JOIN あのテーブル
ON このカラム = あのカラム
where なじょうけん ;

$sth=$dbh -> query($sql);  //ふつうにクエリ実行

$result = $sth->fetchAll(); //一度フェッチする

$array_size= count($result); //なければ 0がかえってくる

foreach ($result as $key => $value) {  //こいつを回す。
if($key==0){   // ループの一回目は普通ゼロ
foreach ($value  as $k => $v) {
$csvth[]= $k ;  //連想配列なのでキーを取り出して配列化
}
fputcsv($fp, $csvth);  //配列をCSVに
}
fputcsv($fp, $value);  //ここからは値行を一行目から
}

フェッチしないで$sthにさわるとポインタが送られてしまうので全部取り出せなくなる。

日付フォーマットをハイフン区切りに

datetimepicekrで入れた日付がMySqlに入らない。

→datetimepicekr.jsを開いて直接編集パターン

142  format:            'Y-m-d',
204  id:'ここに独自ID',
205  roundTime:'round', // ceil, floor
206  className:'独自クラス',

→PHPで直すパターン
datetimepicker timestampと同じ型にすれば入るだろう。

2015/02/28 17:20:00 → 2015-02-28 17:20:00

date( "Y-m-d H:i:s",strtotime( $_POST["dhms"] ))

これでスッキリ解決。

SQL文 LOAD DATA INFILEまとめ

行数の多いテキストファイルを超高速でテーブルに取り込むSQL文。

数万件合ってもコンマ数秒で取り込むので使わないわけにはいかない。INSERTではとてもやってられない。

ハマり所1
読み込むファイルのパーミッションとディレクトリのパーミッション= ともに755にしておく

ハマりの2
LOAD DATA [ local ] INFILE "data.txt" INTO TABLE テーブル名;
このパラメータlocalはMySqlユーザーにFILE権限なしでも実行可能にするものなので、入れておくべき。

ハマりの3
改行記号は  LINES TERMINATED BY '\n';  と紹介しているところが多いが、 "\n"としないと一行しか入らなかった。

ハマりの4
コンソールから実行すると上手くいくが、PDOだとエラーになる。
$dbh = new PDO($db,$user,$password,array(PDO::MYSQL_ATTR_LOCAL_INFILE => true));
array(・・)を追加したら取り込めた。

MySQL 既存テーブルにCSVをインポート

そのままインポートすると別の新規テーブルができてしまう。

  1. とりあえず カレントのディレクトリがここらしいのでファイルをコピーする→ /var/lib/mysql/mws/tmp/
  2. ファイルのパーミッションは755でいいらしい。tmpのパーミッションも755にしておく
  3. テーブルまで選択してSQL文
    LOAD DATA INFILE "tmp/data.csv"
    INTO TABLE t_us FIELDS TERMINATED BY ","
    LINES TERMINATED BY "\r"
    IGNORE 1 LINES;
  4. 改行文字のrと先頭の1行がフィールド名なので入れない→IGNORE
    改行文字を間違えるとデータは1行しか入らない。

phpMyAdminアップデート

#cd /usr/share/  ここに移動

# mv phpmyadmin/ _phpmyadmin/  名前を変えてバックアップ

# cd /home/usr/ユーザ名 ここにダウンロードするのでカレントを移動

# wget http://sourceforge.net/projects/phpmyadmin/files/phpMyAdmin/4.2.12/phpMyAdmin-4.2.12-all-languages.tar.gz

# tar phpMyAdmin-4.2.12-all-languages.tar.gz    解凍

# mv phpMyAdmin-4.2.12-all-languages/ phpMyaAdmin/    ディレクトリ名変更

# chmod 660 /home/ユーザ名/phpMyAdmin/config.inc.php
# cp -r /home/ユーザ名/phpMyAdmin/ /usr/share/phpMyAdmin/
ディレクトリごとコピー
# cp /usr/share/_phpMyAdmin/config.inc.php /usr/share/phpMyAdmin/config.inc.php
以前の設定ファイルをコピー
# chown -R root.apache /usr/share/phpMyAdmin/
オーナー変更して完了

MySqlテーブルに空のレコードが入る

PHPからPDOでデータ接続し、INSERT文でデータを挿入した際に正常なデータが一件と、付随して空のレコードが入る。
PHPコードのheadにあったリンク切れのファビコンを削除したら直った ←これが原因

SQL文を最速にする11のポイント

SQL文を最速にする11のポイント

 たとえ最終的な結果が同じでも,SQL文は書き方一つでパフォーマンスがずいぶんと変わってきます。ここでは,速いSQL文を記述するためのポイ ントや注意点をいくつか紹介しておきましょう。

●WHEREの左辺で算術演算子や関数を使わない

 WHERE句の左辺に算術演算や関数を指定すると,インデックスが使われません。例えば,
SELECT NAME FROM CUSTOMERS
WHERE SAL - TAX > 1000
とすると,たとえSALフィールドにインデックスが定義されていてもテーブル全体を走査してしまいます。こうした場合は,
SELECT NAME FROM CUSTOMERS
WHERE SAL > TAX + 1000
のように記述すれば良いでしょう。

●「後方一致」検索はなるべく避ける

 インデックスが付加されているフィールドであっても,LIKE '%AAA' のような「後方一致」を指定すると,インデックスを検索せずにデータ部の全表走査が行われます。したがって「後方一致」の使用はなるべく避けるようにしま しょう。どうしても必要であるなら,
・何らかの,少量まで絞り込める条件とAND条件で組み合わせる
・複数のフィールドに分割し,少しでも前方・完全一致できる範囲を広げる
といった方法を検討して下さい。

●IS NULL,IS NOT NULLを単独で使わない

 条件を表すWHERE句にIS NULL/IS NOT NULLを指定したときは,インデックスを定義したフィールドであっても,全表走査が行われます。したがって,これらの条件を指定するときは,単独で指定 するのではなく,何らかのかなり絞り込める条件を合わせて指定してください。例えば,問い合わせの結果を変更せずに「B = 10」の条件を付加できるなら
…WHERE A IS NULL
とする代わりに
…WHERE A IS NULL AND B = 10
とします。

●SELECT文で「*」を使わない

 レコード長が長いときや,フィールド数が多いときには,すべてのフィールドを表す「*」を指定するのはできるだけ避けて,使用するフィールドだけ を指定するようにします。「*」を指定すると,参照系のSQL文では,すべてのフィールドを繰り返してコピーするため,リソースを無駄に使うことになりま す。最低限度必要なフィールドだけを指定するのが基本です。

●ORはある程度絞り込んでから使う

 論理演算子ORを使用した場合,一応インデックスが使用されるものの,個々の条件が抽出する件数が少ない(数%程度)状態でないと,あまり効果が ありません。

●GROUP BY,ORDER BY,HAVINGは注意する

 GROUP BY句,ORDER BY句,HAVING句は,余分なディスク入出力が発生したりディスク領域を使うので,自分もしくはほかのプログラムのパフォーマンスに悪影響を及ぼしま す。このことを念頭において,使わずに済むならなるべく使わないようにしましょう。

●演算子の組み合わせで速度が変わる

 検索条件に,「>」「<」「=」をANDで組み合わせるときは,指定の仕方によってインデックスの使われ方が異なります。等号と不等号の組み合わ せは,等号のみインデックスが使われます。例えば,
SELECT NAME FROM CUSTOMERS
WHERE JOB = 'MANAGER'
AND SAL > 1000
とすると,「JOB = 'MANAGER'」にはインデックスが使われますが,「SAL > 1000」には使われません。また,不等号同士の組み合わせでは,先に指定した条件だけにインデックスが使われます。つまり
SELECT NAME FROM CUSTOMERS
WHERE TAX > 100
AND SAL > 1000
のSQL文では,RDBMSは「TAX > 100」だけにインデックスを使い「SAL > 1000」には使いません。

●テーブルの別名を利用する

 テーブルに別名をつけて,フィールド名にはその別名をつけると,SQL文の解析処理を減らすことができます。例えば,
SELECT ID, NAME FROM CUSTOMERS
WHERE SAL < 1000
よりも,
SELECT a.ID, a.NAME FROM CUSTOMERS a
WHERE SAL < 1000
のほうが高速になります。

●SQL文の表現を統一する

 本文中で述べたように,RDBMSは実行計画をキャッシュに保存しておいて再利用します。ところが,SQL文に定数を直接記述してしまう と,RDBMSは定数値だけが異なるSQL文を別のものと解釈するため,再利用されません*B。 バインド変数を使用して,できる限りSQL文を統一するようにします。また,文字の大小や記述の仕方なども統一しておかないと別のSQL文だと認識されて しまうので,気を付けてください。

●SQL文を簡潔に記述する

 SQL文はなるべく簡潔に記述するようにします。そうすることで,SQL文の処理時間を短縮することができます。

mysqlのストアドプロシージャ

delimiter //
CREATE PROCEDURE get_comment(IN id INT)
BEGIN 
select content from comment where entry_id = id;
END
  • データベース名 stad
  • テーブル名 : comment
  • テーブル名 : entry
  • これで作成↓
CREATE TABLE entry (
    id int auto_increment primary key,
    title varchar(255),
    body text,
    delete_flag tinyint default 0
);
 
CREATE TABLE comment (
    id int auto_increment primary key,
    name varchar(255),
    content varchar(255),
    entry_id int not null
);

これでentry_idが1のレコードをselectできる。

call get_comment(1);

delimiter //
CREATE PROCEDURE get_category(IN id INT)
BEGIN 
select * from m_shohin where cat_ID = id;
END

ストアドプロシージャの削除

DROP PROCEDURE get_category

MySqlの文字化けをなおす

データベースへ挿入した日本語が文字化けする場合の対応策

テーブルのエンコードはutf8_general_ciで作成したとする。

 以下のこれらのコメントアウトをはずす
my.ini の変更 MySqlの文字化けは実際これのみでOK )

[mysqld]
 character-set-server=utf8
 collation-server = utf8_general_ci
 init-connect = SET NAMES utf8
 skip-character-set-client-handshake

[mysqldump]
 quickmax_allowed_packet = 16M
 default-character-set=utf8

[mysql]
 no-auto-rehash
 # Remove the next comment character if you are not familiar with SQL
 #safe-updates
 default-character-set=utf8

これで文字化けが治った。

MySQLへのインポート容量制限

PhpMyadmminでデータベースをインポートする際に「phpの容量制限がどうの」と警告が出る。
3M程度のファイルだが、容量制限があってできないというもの。
対処方法
Xamppならコントロール→MySqlの「config」→php.iniを開く
以下の2箇所を変更。

; Maximum size of POST data that PHP will accept.
post_max_size = 10M

; Maximum size of POST data that PHP will accept.
post_max_size = 10M