おやじプログラマー日記: MySQL

【MySQL】文字列検索の大文字・小文字の区別の注意点について

文字列検索において、データ型が「非バイナリ文字列」（CHAR、VARCHAR、VARCHAR）の場合には比較対象の照合順序が使用されます。

データ型が「バイナリ文字列」（BINARY、VARBINARY、BLOB）の場合の検索には比較対象のバイト値が使用されます。

尚、「非バイナリ文字列」と「バイナリ文字列」の比較では、「バイナリ文字列」の比較で処理されます。

「非バイナリ文字列」の比較で問題が発生する場合は、以下の様な場合です。
仮に以下の様なテーブルが在ったとします。尚、このテーブルの文字コードは「latin1」になっているとします。

以下の様に一部一致検索（Like）を行います。
普通に考えると「A%」なので大文字「A」を含むものを検索されるのですが、小文字「a」も対象となって結果が表示されています。
文字コード「latin1」の照合順序ではアルファベットの大文字・小文字を区別しないため、この様なことが起こります。

そこで以下の様にSQLを変更してみます。
条件の対象となる「ADDCOL1」の照合順序を COLLATE で強制的に「latin1」のバイナリ文字列である「latin1_bin」と指定すると、大文字・小文字が区別されます。

投稿時刻 15:39 MySQL | 個別ページ | コメント (0) | トラックバック (0)

【MySQL】カラムの中のカンマ区切りのコードチェックは FIND_IN_SET 関数を使用する

コードがカンマで連結して格納されているカラムに、指定するコードが存在するかをチェックするには FIND_IN_SET 関数が便利です。関数の使用方法は以下となります。

・FIND_IN_SET(str, strlist)
　戻り値：strlist 中の文字列の存在する順番を 1 ～ N で返す。
　　　　　見つからない場合は「0」を返す。
　str：検索文字列
　strlist：検索対象のカンマ区切り文字列

実際の例を以下に示します。

最初のSELECT文では文字列 '20' の位置である「2」が返されますが、コードの存在しない場合には「0」が返されます。

また、この FIND_IN_SET 関数を WHERE 句で使うことができます。
以下の様なテーブルを仮に作成しました。

このテーブルの「COL1」に対してコード「20」を持つ行を選択してみます。

投稿時刻 15:26 MySQL | 個別ページ | コメント (0) | トラックバック (0)

【MySQL】テーブルカラムは大文字・小文字の区別が無いので注意が必要

とあるプログラム中で、テーブル作成では大文字でカラム名が定義されているカラムに対して、データ取得（SELECT）で気が付かず小文字で書いてしまっていたのですが、問題無く処理されていた様でした。
実は MySQL ではカラム名の記述で大文字・小文字の区別はしていないのです。

実際に処理を見てみます。前回の記事で AUTO_INCREMENT に関することを書きましたが、そこでのテーブルを使用します。
テーブル作成のSQL文は以下の通りです。

このテーブルはカラムが大文字で宣言されていますが、以下カラムを小文字で取得する様にしてみます。

取得するカラム名が大文字、小文字のどちらであっても問題無く取得できます。

ただし、 PHP からデータを取得した時には、１行のデータを配列で扱いますが、その時の指標として各カラム名を使ったりします。
PHP では配列の指標では大文字と小文字の区別されるため、どちらかに統一するほうが良いと思います。
（例えば $row["id"] と $row["ID"] は異なります。）

■関連記事
⇒【MySQL】テーブルカラムは大文字・小文字の区別が無いので注意が必要
⇒【MySQL】テーブル作成時にカラムにAUTO_INCREMENTを設定する方法について
⇒【MySQL】テーブルカラムに後からAUTO_INCREMENTを設定する方法について
⇒【MySQL】文字列カラムに追加で更新する方法について(CONCAT)
⇒【MySQL】既に存在するTABLEに複数カラムを位置指定で追加する方法について(ALTERTABLE)

投稿時刻 11:06 MySQL | 個別ページ | コメント (0) | トラックバック (0)

【MySQL】テーブル作成時にカラムにAUTO_INCREMENTを設定する方法について

テーブル作成時に、カラムに主キーとして設定し AUTO_INCREMENT に設定する場合は以下のSQL文で作成できます。

AUTO_INCREMENT の設定されたテーブルへの追加は以下の様に行います。
AUTO_INCREMENT のカラムへ値を設定しなくても、自動で値が設定されていきます。

上の実行からも分かる様に、気をつけたいのは AUTO_INCREMENT のカラムへ途中で値を設定した場合には、その値から AUTO_INCREMENT の値が開始されます。

■関連記事
⇒【MySQL】テーブルカラムは大文字・小文字の区別が無いので注意が必要
⇒【MySQL】テーブル作成時にカラムにAUTO_INCREMENTを設定する方法について
⇒【MySQL】テーブルカラムに後からAUTO_INCREMENTを設定する方法について
⇒【MySQL】文字列カラムに追加で更新する方法について(CONCAT)
⇒【MySQL】既に存在するTABLEに複数カラムを位置指定で追加する方法について(ALTERTABLE)

投稿時刻 13:41 MySQL | 個別ページ | コメント (0) | トラックバック (0)

【MySQL】テーブルカラムに後からAUTO_INCREMENTを設定する方法について

仕事の中、テーブルカラムに主キーは設定していたのですが AUTO_INCREMENT に設定する必要が出てきたので調べてみました。
以下のSQL文で設定できます。

但し、これは指定された「カラム名」が主キーの設定がされていないとエラーが発生しますので、以下の様に主キーの設定も合わせて行います。

■関連記事
⇒【MySQL】テーブルカラムは大文字・小文字の区別が無いので注意が必要
⇒【MySQL】テーブル作成時にカラムにAUTO_INCREMENTを設定する方法について
⇒【MySQL】テーブルカラムに後からAUTO_INCREMENTを設定する方法について
⇒【MySQL】文字列カラムに追加で更新する方法について(CONCAT)
⇒【MySQL】既に存在するTABLEに複数カラムを位置指定で追加する方法について(ALTERTABLE)

投稿時刻 11:33 MySQL | 個別ページ | コメント (2) | トラックバック (0)

【MySQL】ダミーテーブルをUNIONで連結してコード表を作成する方法

MySQL では以下の様にダミーテーブルが使えます。

ダミーテーブルの名前として DUAL を使って SELECT の中のカラムを適当なものが記述できます。また、 DUAL が無くても動作します。

そこで、コード表とするために、複数のダミーテーブルからの取得を UNION で連結します。
以下に、3個のコードと名前のセットを返すSQLを記します。

普通はコードマスタか何かでデータを持ちますが、特殊なコードを追加したい場合に、今回の様にダミーテーブルのデータを UNION で連結することがあります。

投稿時刻 16:20 MySQL | 個別ページ | コメント (0) | トラックバック (0)

【MySQL】ユーザー変数を使ってSELECTに行番号を付加する方法

MySQL ではユーザー変数を定義しそれを SELECT 文のなかで使えます。
以下のSQLの実行は行番号を付加する例です。
SET命令でユーザー変数 @NUM を宣言し、その変数を SELECT で使用しています。

結果表示を見れば、行番号として値が表示されていることが分かります。

SET命令と SELECT文の2行が少し気になるので、調べてみたら以下の様に、SET命令の部分をダミーのテーブルでの宣言として一体化できます。

GROUP BY や ORDER BY 句を行った場合にも行番号は付加できる様です。

但し、ユーザ変数の計算部分の処理が1行毎最初に処理される様に思いますが、クエリー結果でその処理順番が保障されるとも限りません。この辺りは、個々の場合で調べる必要があります。

投稿時刻 14:48 MySQL | 個別ページ | コメント (0) | トラックバック (0)

【MySQL】カラム名を囲むバッククオートについて

いつも何気なくカラムを使う時には、ほぼカラム名をバッククオート「`」で囲んでいます。
バッククオートは無くても問題は無いのですが、全角文字をカラムにしている場合は経験上から気持ち悪いのでそうしています。

Oracleなんかだと、バッククオートではなくダブルクォートで囲みます。

以下の記事のテーブルを例にバッククオートでの囲み方を示します。

⇒【MySQL】既に存在するTABLEに複数カラムを位置指定で追加する方法について(ALTER TABLE)

最初の SELECT 文はバッククオート無し TEST1 テーブルの全てを検索していますが、 2番目の SELECT ではカラム及び、テーブル名をバッククオートで囲っています。

バッククオートですが何が有利なのかと言えば、カラム名が予約語でもOKなのです。（普通は予約語をカラム名にしませんが）
以下の例を見て下さい。カラム名には相応しくない INSERT ですが、取り敢えずテーブルに追加して SELECT してみます。

MariaDB [test]> ALTER TABLE `TEST1` ADD COLUMN `INSERT` VARCHAR(16) NULL;
Query OK, 0 rows affected (0.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> DESC `TEST1`;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| COL1    | int(11)     | YES  |     | NULL    |       |
| COL2    | int(11)     | YES  |     | NULL    |       |
| ADDCOL1 | varchar(16) | YES  |     | NULL    |       |
| ADDCOL2 | varchar(32) | YES  |     | NULL    |       |
| COL3    | int(11)     | YES  |     | NULL    |       |
| INSERT  | varchar(16) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

MariaDB [test]> SELECT `INSERT`, `TT`.*  FROM `TEST1` `TT`;
+--------+------+------+---------+---------+------+--------+
| INSERT | COL1 | COL2 | ADDCOL1 | ADDCOL2 | COL3 | INSERT |
+--------+------+------+---------+---------+------+--------+
| NULL   | 1001 | 2001 | AA01    | BB01    | 3001 | NULL   |
| NULL   | 1002 | 2002 | AA02    | BB02    | 3002 | NULL   |
+--------+------+------+---------+---------+------+--------+
2 rows in set (0.06 sec)

結果は問題無く出来る様です。（しかし、予約語はいけませんが。）

バッククオートは少し面倒くさいのですが、カラム名などをはっきりと区別できる点もありますし、使った方が良いと思います。（テーブルの別名にもバッククオートは付加できます）

投稿時刻 13:19 MySQL | 個別ページ | コメント (0) | トラックバック (0)

【MySQL】既に存在するTABLEに複数カラムを位置指定で追加する方法について(ALTER TABLE)

仕様変更などでテーブルのカラム追加が発生しますが、この時複数のカラムを位置指定で行う方法について記します。

先ずは、最初に例となるテーブルを以下の様に作成します。このテーブルはテストなので、特に主キーなどは追加しません。

このテーブルの COL2 の次に ADDCOL1 、 ADDCOL2 と言う名前で VARCHAR 属性で追加します。

これを実行してテーブルを見てみると、カラムが追加されたことが分かります。

投稿時刻 10:54 MySQL | 個別ページ | コメント (0) | トラックバック (0)

【MySQL】文字列カラムに追加で更新する方法について(CONCAT)

VARCHAR や TEXT で定義されている文字列カラムに対して、文字列の追加更新を行う場合には以下の様に CONCAT 関数を使って更新を行います。

この方法で一見よさそうなのですが、更新対象のカラムが元々 NULL の場合には結果として NULL と更新されてしまいます。

CONCAT 関数は連結するデータが NULL の場合には NULL を返すため、結果として対象のカラムが NULL に更新されます。

そこで以下の様に IFNULL 関数で NULL の場合には空文字（''）の判定を行ってやります。

これで何とか目的の更新処理が行えると思います。

投稿時刻 16:44 MySQL | 個別ページ | コメント (0) | トラックバック (0)

おやじプログラマー日記

コンピュータ業界に身をおいて３０年あまり…VB.NET・OracleなどのTIPS、おやじの独り言

おすすめ本

最近の記事

最近のトラックバック

Ranking

最近のコメント

アーカイブ

AccRanking

Ad

カテゴリ「MySQL」の13件の記事

2020年7月30日 (木)

【MySQL】文字列検索の大文字・小文字の区別の注意点について

2020年4月28日 (火)

【MySQL】カラムの中のカンマ区切りのコードチェックは FIND_IN_SET 関数を使用する

2020年3月 5日 (木)

【MySQL】テーブルカラムは大文字・小文字の区別が無いので注意が必要

2019年11月19日 (火)

【MySQL】テーブル作成時にカラムにAUTO_INCREMENTを設定する方法について

【MySQL】テーブルカラムに後からAUTO_INCREMENTを設定する方法について

2019年7月 6日 (土)

【MySQL】ダミーテーブルをUNIONで連結してコード表を作成する方法

2019年7月 3日 (水)

【MySQL】ユーザー変数を使ってSELECTに行番号を付加する方法

【MySQL】カラム名を囲むバッククオートについて

【MySQL】既に存在するTABLEに複数カラムを位置指定で追加する方法について(ALTER TABLE)

2019年6月 1日 (土)

【MySQL】文字列カラムに追加で更新する方法について(CONCAT)

検索

admax

カテゴリ

リンク

LSF

LPOP