mysqlのその他の関数

1. ビット関数

ビット演算の場合、MySQL は BIGINT (64 ビット) アルゴリズムを使用するため、これらの演算子の最大範囲は 64 ビットです。

|

ビットごとの OR:

mysql> SELECT 15;

-> 結果は 64 ビットの符号なし整数です。

&

ビットごとの AND:

mysql> SELECT 29 & 15;

-> 結果は 64 ビットの符号なし整数です。

^

ビットごとの XOR:

-> SELECT 11 ^ 3;

結果は 64 ビットの符号なし整数です。

<<

longlong (BIGINT) 数値を左に 2 桁シフトします。

mysql> SELECT 1 << 2;

-> 4

結果は 64 ビットの符号なし整数です。

>>

longlong (BIGINT) 数値を右に 2 桁シフトします。

mysql> SELECT 4 >> 2;

-> 1

結果は 64 ビットの符号なし整数です。

~

すべてのビットを反転します。

mysql> SELECT 5 & ~1;

-> 結果は 64 ビットの符号なし整数です。

BIT_COUNT(N)

パラメータ N に設定されたビット数を返します

mysql> SELECT BIT_COUNT(29);

2. このセクションでは、暗号化と暗号化された値について説明します。任意のバイト値を含む可能性のある暗号化関数によって返された一部の結果を保存する場合は、CHAR 列または VARCHAR 列の代わりに BLOB 列を使用して、末尾のスペースの削除により一部のデータ値が変更されるという潜在的な問題を回避します。

AES_ENCRYPT(str,key_str) 、AES_DECRYPT(crypt_str,key_str)

これらの関数では、以前は「Rijndael」として知られていた公式の AES (Advanced Encryption Standard) アルゴリズムを使用した暗号化とデータ暗号化が可能になります。秘密鍵の長さは 128 ビットですが、ソースを変更することで 256 ビットまで拡張できます。 128 ビットを選択したのは、その方がはるかに高速であり、ほとんどの目的にはこのレベルの機密性で十分であるためです。

入力パラメータは任意の長さにすることができます。いずれかのパラメータが NULL の場合、関数の結果も NULL になります。

AES はブロックレベルのアルゴリズムであるため、長さのバランスが取れていない文字列をエンコードするためにパディングが使用されるため、結果の文字列の長さは 16 * (trunc(string_length / 16) + 1) になります。

AES_DECRYPT() が無効なデータまたは不正なパディングを検出した場合、NULL を返します。ただし、入力された情報またはパスワードが無効な場合、AES_DECRYPT() は NULL 以外の値 (役に立たない情報である可能性があります) を返す場合があります。

クエリを変更することで、AES 関数を使用して暗号化された形式でデータを保存できます:

INSERT INTO t VALUES (1,AES_ENCRYPT('text','password'));

AES_ENCRYPT() および AES_DECRYPT () MySQL のユニバーサル パスワードの最も安全な暗号化関数とみなされます。

DECODE(crypt_str,pass_str)

暗号化された文字列 crypt_str を復号化するパスワードとして pass_str を使用します。crypt_str は ENCODE() によって返される文字列である必要があります。

ENCODE(str, pass_str)

パスワードとして pass_str を使用して str を復号化します。 DECODE() を使用して結果を復号化します。

結果は、str と同じ長さのバイナリ文字列です。列に保持したい場合は、BLOB 列タイプを使用します。

DES_DECRYPT(crypt_str[,key_str])

文字列を暗号化するには DES_ENCRYPT() を使用します。エラーが発生した場合、この関数は NULL を返します。

この関数は、MySQL が SSL サポートで構成されている場合にのみ機能することに注意してください。

key_str パラメータが指定されていない場合、DES_DECRYPT() は最初に暗号化された文字列の最初のバイトをチェックして、元の文字列の暗号化に使用された DES パスワード キー番号を特定し、次に DES キー ファイルの単語からキーを読み取り、復号化します。情報。これを実行するには、ユーザーは SUPER 権限を持っている必要があります。 --des-key-file サーバーを選択してキー ファイルを指定できます。

この関数に key_str パラメータを渡すと、その文字列がメッセージを復号化するためのキーとして使用されます。

crypt_str パラメータが暗号化された文字列ではない場合、MySQL は指定された crypt_str を返します。

DES_ENCRYPT(str[,(key_num|key_str)])

Triple-DES アルゴリズムによって指定されたキーワードを使用して文字列を暗号化します。エラーが発生した場合、この関数は NULL を返します。

この関数は、MySQL が SSL サポートで構成された後にのみ実行されることに注意してください。

使用する暗号化キーワードの選択は、DES_ENCRYPT() の 2 番目のパラメーター (指定されている場合) に基づきます:

パラメーターの説明

パラメータなし DES キーワード ファイルの最初のキーワードを使用します。

key_num は、DES キーワード ファイルで指定されたキー番号 (0 ～ 9) を使用します。

key_str 指定されたキー文字列を使用して str を暗号化します。

キーワード ファイルを指定するには、--des-key-file サーバーを選択します。

返される文字列はバイナリ文字列で、最初の文字は CHAR(128 | key_num) です。

Plus 128 を使用すると、暗号化されたキーワードを簡単に識別できます。文字列キーを使用する場合、key_num は 127 です。

結果の文字列の長さは、new_len = orig_len + (8-(orig_len % 8))+1 となります。

DES キーワード ファイルの各行の形式は次のとおりです:

key_num des_key_str

各 key_num は 0 から 0 の範囲の数値である必要があります。ファイル内の行の順序は任意です。 des_key_str は、情報の暗号化に使用される文字列です。番号とキーワードの間には少なくとも 1 つのスペースが必要です。 DES_ENCRYPT() にキーワード引数を指定しない場合、最初のキーワードがデフォルトのキーワードとして使用されます。

FLUSH DES_KEY_FILE ステートメントを使用すると、MySQL にキーワード ファイルから新しいキーワード値を読み取らせることができます。これには、RELOAD 権限が必要です。

デフォルトのキーワード セットを持つ利点の 1 つは、暗号化された列の値をエンド ユーザーに復号化する機能を提供せずに、アプリケーションに暗号化された列の値を検証する方法を提供することです。

mysql> SELECT customer_address FROM customer_table

> WHERE crypted_credit_card = DES_ENCRYPT('credit_card_number');

ENCRYPT(str[,salt])

str を暗号化するには Unix crypt() システム コールを使用します。 Salt パラメータは、少なくとも 2 文字を含む文字列である必要があります。 Salt 引数が指定されていない場合は、任意の値が使用されます。

mysql> SELECT ENCRYPT('hello');

-> 'VxuFAJXVARROc'

少なくとも一部のシステムでは、ENCRYPT() は str の最初の 8 文字を除くすべてを無視します。この動作は、下線付きの crypt() システム コールの実行によって決まります。

お使いのシステムで crypt() が利用できない場合 (Windows の場合と同様)、ENCRYPT() は常に NULL を返します。このため、これら 2 つの関数はすべてのプラットフォームに適しているため、代わりに MD5() または SHA1() を使用することをお勧めします。

MD5(str)

文字列の MD5 128 ビット チェックサムを計算します。値は 32 桁の 16 進数のバイナリ文字列として返されます。パラメータが NULL の場合は、NULL が返されます。たとえば、戻り値をハッシュ キーとして使用できます。

mysql> SELECT MD5('testing'); 文字については、「キャスト関数と演算子」の BINARY 演算子の項目で説明されているバイナリ文字列変換を参照してください。

OLD_PASSWORD(str)

セキュリティ向上のためにPASSWORD()の実装が変更されると、OLD_PASSWORD()がMySQLに追加されます。 OLD_PASSWORD() は古い PASSWORD() 実装 (4.1 より前) の値を返しますが、5.1 MySQL サーバーに接続する必要がある 4.1 より前のクライアントのパスワードを切断せずに設定できます。

PASSWORD(str)

元のパスワードstrからパスワード文字列を計算して返します。パラメータがNULLの場合はNULLを返します。この関数は、暗号化された MySQL パスワードをユーザー認証テーブルの Password 列に保存するために使用されます。

PASSWORD() は、Unix パスワードの暗号化方法とは異なる方法でパスワード暗号化を実行します。 ENCRYPT() を参照してください。

注: PASSWORD() 関数は MySQL Server の認証システムによって使用されます。独自のアプリケーションでは使用しないでください。同じ目的を達成するために、代わりに MD5() または SHA1() を使用できます。アプリケーションでのパスワードと認証の処理の詳細については、RFC 2195 を参照してください。

SHA1(str) SHA(str)

RFC 3174 (セキュア ハッシュ アルゴリズム) と同様に、文字列の SHA1 160 ビット チェックサムを計算します。 。値は 40 桁の 16 進数、または引数が NULL の場合は NULL として返されます。この関数の考えられる使用法の 1 つは、ハッシュ キーとして使用することです。パスワードを保存するパスワードセーフ機能としても利用できます。

mysql> SELECT SHA1('abc'); SHA() と SHA1() は同じ意味です。

3. 情報関数

BENCHMARK(count,expr)

BENCHMARK() 関数は式 expr を実行するために count 回繰り返します。 これは、MySQL が式を処理する速度を計算するために使用できます。結果の値は通常 0 です。もう 1 つの用途は、mysql クライアント内で使用され、クエリの実行数を報告できます。

mysql> SELECT BENCHMARK(1000000,ENCODE('hello','goodbye'));

+----------------------------------------------+
| BENCHMARK(1000000,ENCODE('hello','goodbye')) |
+----------------------------------------------+
|                                            0 |
+----------------------------------------------+
1 row in set (4.74 sec)

ここで報告される時間は、サーバーの CPU 時間ではなく、クライアントの共有時間です。 BENCHMARK() を複数回実行し、サーバー マシンの負荷と関連して結果を解釈することをお勧めします。

・ CHARSET(str)

文字列引数の文字セットを返します。

mysql> SELECT CHARSET('abc');        -> 'latin1'
mysql> SELECT CHARSET(CONVERT('abc' USING utf8));        -> 'utf8'
mysql> SELECT CHARSET(USER());        -> 'utf8'

· COERCIBILITY(str)

文字列引数の圧縮率の整数値を返します。

mysql> SELECT COERCIBILITY('abc' COLLATE latin1_swedish_ci);        -> 0
mysql> SELECT COERCIBILITY(USER());        -> 3
mysql> SELECT COERCIBILITY('abc');        -> 4

戻り値の意味は次のとおりです。

圧縮率 意味 例

0 COLLATE 句を使用した明示的な並べ替え値

1 並べ替えなし 異なる並べ替えによる文字列の連結

2 明示的な並べ替え 列の値

3 システム定数 USER()戻り値

4 圧縮可能なリテラル文字列

5 NULL または NULL によって得られる式は無視されます

以下の値が優先されます。

COLLATION(str)

文字列パラメータのソート方法を返します。

mysql> SELECT COLLATION('abc');

-> 'latin1_swedish_ci'

-> 'utf8_general_ci' TION_ID()

が返すfor 接続の接続 ID (スレッド ID)。各接続には独自の一意の ID があります。

mysql> SELECT CONNECTION_ID();

-> 23786

CURRENT_USER, CURRENT_USER()

現在のセッションの検証済みのユーザー名とホスト名の組み合わせを返します。この値は、アクセス権を決定する MySQL アカウントに対応します。 SQL SECURITY DEFINER 特性が割り当てられたストアド プロシージャ内では、CURRENT_USER() はプロシージャの作成者を返します。

CURRENT_USER() の値は USER() の値とは異なる場合があります。

mysql> SELECT USER (); mysql'

この例では、( USER() 関数)、サーバー 匿名ユーザー アカウントを使用してクライアントを識別します ( CURRENT_USER() の空のユーザー名のセクションを参照)。この問題が発生する理由の 1 つは、One が davida に対する認証リストに十分なアカウントを持っていないことです。

CURRENT_USER() 返される文字列には utf8 文字セットが使用されます。

DATABASE()

utf8 文字セットを使用してデフォルト (現在の) データベース名を返します。ストアド プロシージャでは、デフォルト データベースはプロシージャに関連付けられたデータベースですが、呼び出しコンテキストのデフォルト データベースと必ずしも同じである必要はありません。

mysql> SELECT DATABASE();

-> 'test'

デフォルトのデータベースがない場合、DATABASE() は NULL を返します。

FOUND_ROWS()

SELECT ステートメントには、サーバーからクライアントに返される行数を制限する LIMIT 句が含まれる場合があります。場合によっては、ステートメントを再度実行せずに、LIMIT なしでステートメントが返した行数を知る必要があります。この行数を確認するには、SELECT ステートメントで SQL_CALC_FOUND_ROWS を選択してから FOUND_ROWS() を呼び出します。 ; FOUND_ROWSを選択() ;

2 番目の SELECT は、最初の SELECT が LIMIT 句なしで返した行数を示す数値を返します (上記の SELECT ステートメントに SQL_CALC_FOUND_ROWS オプションが含まれていない場合は、LIMIT が使用され、FOUND_ROWS は使用されません) () は異なる値を返す場合があります結果）。

FOUND_ROWS() によって渡される有効行数は瞬間的なものであり、SELECT SQL_CALC_FOUND_ROWS ステートメントに続くステートメントをスキップするためには使用されません。後でこの値を参照する必要がある場合は、次のように保存します。

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM ... ;

mysql> SET @rows = FOUND_ROWS(); SELECT SQL_CALC_FOUND_ROWS を使用している場合、MySQL は計算す​​る必要があります。結果セット全体の行数が少なくなります。ただし、結果セットをクライアントに送信する必要がないため、LIMIT を指定せずにクエリを再度実行するよりも高速です。

SQL_CALC_FOUND_ROWS 和 FOUND_ROWS() 在当你希望限制一个问询返回的行数时很有用，同时还能不需要再次运行问询而确定全部结果集合中的行数。一个例子就是提供页式显示的Web脚本，该显示包含显示搜索结果其它部分的页的连接。使用FOUND_ROWS() 使你确定剩下的结果需要多少其它的页。

SQL_CALC_FOUND_ROWS 和 FOUND_ROWS() 的应用对于UNION 问询比对于简单SELECT 语句更为复杂，原因是在UNION 中，LIMIT 可能会出现在多个位置。它可能适用于UNION中的个人 SELECT语句，或是总体上 到UNION 结果的全程。

SQL_CALC_FOUND_ROWS对于 UNION的意向是它应该不需要全程LIMIT而返回应返回的行数。SQL_CALC_FOUND_ROWS 和UNION 一同使用的条件是：

SQL_CALC_FOUND_ROWS 关键词必须出现在UNION的第一个 SELECT中。

FOUND_ROWS()的值只有在使用 UNION ALL时才是精确的。若使用不带ALL的UNION，则会发生两次删除， 而 FOUND_ROWS() 的指只需近似的。

假若UNION 中没有出现 LIMIT ，则SQL_CALC_FOUND_ROWS 被忽略，返回临时表中的创建的用来处理UNION的行数。

LAST_INSERT_ID() LAST_INSERT_ID(expr)

自动返回最后一个INSERT或 UPDATE 问询为 AUTO_INCREMENT列设置的第一个 发生的值。

mysql> SELECT LAST_INSERT_ID();

-> 195

产生的ID 每次连接后保存在服务器中。这意味着函数向一个给定客户端返回的值是该客户端产生对影响AUTO_INCREMENT列的最新语句第一个 AUTO_INCREMENT值的。这个值不能被其它客户端影响，即使它们产生它们自己的 AUTO_INCREMENT值。这个行为保证了你能够找回自己的 ID 而不用担心其它客户端的活动，而且不需要加锁或处理。

假如你使用一个非“magic”值来更新某一行的AUTO_INCREMENT 列，则LAST_INSERT_ID() 的值不会变化(换言之, 一个不是 NULL也不是 0的值)。

重点: 假如你使用单INSERT语句插入多个行， LAST_INSERT_ID() 只返回插入的第一行产生的值。其原因是这使依靠其它服务器复制同样的 INSERT语句变得简单。

例如：

mysql> USE test;

Database changed
mysql> CREATE TABLE t (    ->   id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,    ->   name VARCHAR(10) NOT NULL    -> );Query OK, 0 rows affected (0.09 sec)
mysql> INSERT INTO t VALUES (NULL, 'Bob');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM t;+----+------+
| id | name |
+----+------+
|  1 | Bob  |
+----+------+
1 row in set (0.01 sec)
mysql> SELECT LAST_INSERT_ID();+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO t VALUES    -> (NULL, 'Mary'), (NULL, 'Jane'), (NULL, 'Lisa');Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM t;
+----+------+
| id | name |
+----+------+
|  1 | Bob  |
|  2 | Mary |
|  3 | Jane |
|  4 | Lisa |
+----+------+
4 rows in set (0.01 sec)
mysql> SELECT LAST_INSERT_ID();+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

虽然第二个问询将3 个新行插入 t, 对这些行的第一行产生的 ID 为 2, 这也是 LAST_INSERT_ID()返回的值。

假如你使用 INSERT IGNORE而记录被忽略，则AUTO_INCREMENT 计数器不会增量，而 LAST_INSERT_ID() 返回0,这反映出没有插入任何记录。

若给出作为到LAST_INSERT_ID()的参数expr ，则参数的值被函数返回，并作为被LAST_INSERT_ID()返回的下一个值而被记忆。这可用于模拟序列：

创建一个表，用来控制顺序计数器并使其初始化：

o                     mysql> CREATE TABLE sequence (id INT NOT NULL);

o                     mysql> INSERT INTO sequence VALUES (0);

使用该表产生这样的序列数 ：

o                     mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);

o                     mysql> SELECT LAST_INSERT_ID();

UPDATE 语句会增加顺序计数器并引发向LAST_INSERT_ID()  的下一次调用，用来返回升级后的值。 SELECT 语句会检索这个值。  mysql_insert_id() C API函数也可用于获取这个值。

你可以不用调用LAST_INSERT_ID()而产生序列，但这样使用这个函数的效用在于 ID值被保存在服务器中，作为自动产生的值。它适用于多个用户，原因是多个用户均可使用 UPDATE语句并用SELECT语句(或mysql_insert_id()),得到他们自己的序列值，而不会影响其它产生他们自己的序列值的客户端或被其它产生他们自己的序列值的客户端所影响。

注意， mysql_insert_id() 仅会在INSERT 和UPDATE语句后面被升级， 因此你不能在执行了其它诸如SELECT或 SET 这样的SQL语句后使用 C API 函数来找回 LAST_INSERT_ID(expr) 对应的值。

ROW_COUNT()

ROW_COUNT() は、前のステートメントによってアップグレード、挿入、または削除された行の数を返します。 この行数は、mysql クライアントによって表示される行数、および mysql_affected_rows() C API 関数によって返される値と同じです。

mysql> INSERT INTO t VALUES(1),(2),(3);

クエリが完了しました。テーブルには 3 行があります (0.00 秒)

レコード: 3 繰り返し: 0 警告: 0

mysql> SELECT ROW_COUNT();
+-------------+
|
+-----------+
| ------------+

|
+---------- -- +
| 2 |
+---------------+

-> 1

mysql> 1

mysql> SELECT RELEASE_LOCK('lock1');

ロック 'lock1' は 2 番目の GET_LOCK() 呼び出しによってロックが解除されているため、2 番目の RELEASE_LOCK( ) 呼び出しは NULL を返すことに注意してください。

INET_ATON(expr)

mysql> SELECT INET_ATON('209.207.224.40');

-> 3520061480

生成される数値は常にネットワーク バイト オーダーです。上記の例と同様に、数値は 209×2563 + 207×2562 + 224×256 + 40 として計算されます。

INET_ATON() は短い形式の IP アドレスも理解します:

mysql> SELECT INET_ATON('127.0.0.1'), INET_ATON('127.1');
-> 2130706433, 2130706433

注: by INET_ATON( )、INT UNSIGNED 列を使用することをお勧めします。 (符号付き) INT 列を使用する場合、最初のオクテットが 127 より大きい対応する IP アドレス値は 2147483647 (つまり、INET_ATON('127.255.255.255') によって返される値) で終了します。

INET_NTOA(expr)

数値ネットワーク アドレス (4 ビットまたは 8 ビット) を指定すると、アドレスの電気的アドレス表現を文字列として返します。

mysql> SELECT INET_NTOA(3520061480);
-> '209.207.224.40'

IS_FREE_LOCK(str)

str という名前のロックが使用できるかどうか (つまり、ブロックされていないかどうか) を確認します。ロックが使用できる場合は 1 (誰もこのロックを使用していない) を返し、ロックが使用されている場合は 0 を返し、エラーが発生した場合 (パラメータが間違っているなど) は NULL を返します。

IS_USED_LOCK(str)

str という名前のロックが使用中かどうか (つまり、ブロックされているかどうか) を確認します。ブロックされた場合は、ロックを使用しているクライアントの接続識別子を返します。それ以外の場合は NULL を返します。

MASTER_POS_WAIT(log_name,log_pos[,timeout])

この関数は、マスターとスレーブの同期を制御するのに非常に便利です。ホスト レコード内のすべての補足情報が読み取られ、デバイスから指定された場所に適用されるまで、ブロックされたままになります。戻り値は、指定された場所に到達するまで待機する必要があるログに記録されたイベントの数です。スレーブ SQL スレッドが開始されていない場合、スレーブ ホスト情報が初期化されていない場合、パラメータが正しくない場合、またはエラーが発生した場合、関数は NULL を返します。タイムアウトを超えると、-1 が返されます。 MASTER_POS_WAIT() の待機中にスレーブ SQL スレッドが終了した場合、この関数は NULL を返します。スレーブデバイスが指定された場所を通過すると、関数はすぐに結果を返します。

タイムアウト値が指定されている場合、MASTER_POS_WAIT()はタイムアウト秒が経過すると待機を停止します。タイムアウトは 0 より大きくなければなりません。ゼロまたは負のタイムアウトはスーパーマーケット時間がないことを示します。

NAME_CONST(name,value)

指定された値を返します。 結果セット列の生成に NAME_CONST() を使用すると、列で指定された名前が使用されます。

mysql> SELECT NAME_CONST('myname', 14);

+--------+
| 私の名前 |
+--------+
| ----------+

mysql> SELECT UUID();

-> '6ccd780c-baba-1026-9564-0040f4311e29'

UUID() はコピー関数をサポートしていないことに注意してください。

VALUES(col_name)

INSERT … ON DUPLICATE KEY UPDATE … ステートメントでは、UPDATE 句で VALUES(col_name) 関数を使用して、ステートメントの INSERT 部分から列の値にアクセスできます。つまり、UPDATE 句の VALUES(col_name) は、挿入する必要があるcol_name の値にアクセスするため、重複キーの競合は発生しません。この関数は、複数行の挿入に特に役立ちます。 VALUES() 関数は INSERT ... UPDATE ステートメントでのみ意味を持ち、その他の場合には NULL のみを返します。

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)

-> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b) ;