mysqlファジイマッチングlikeおよび一括置換replace
5455 ワード
1、mysqlファジイマッチングlikeとnot likeの使い方:
SELECT*FROM`user`where`nickname`LIKE'%テスト%'
SELECT*FROM`user`where`nickname`not LIKE'%テスト%'
2、mysql一括置換replace関数の使い方:
はフィールドを置換、 を置換することができる.は、元のテーブルにそのデータがある場合、そのデータを削除するテーブルの行を置き換えます.元のテーブルにこのデータがない場合、指定値 が挿入されます.
mysql> select replace('12_34_56','_','&');+-----------------------------+| replace('12_34_56','_','&') |+-----------------------------+| 12&34&56 |+-----------------------------+
mysql> select * from user;+----+--------------+---------------+------+-------------+----------+---------+| ID | user_id_tree | nickname | NAME | DESCRIPTION | QUANTITY | created |+----+--------------+---------------+------+-------------+----------+---------+| 1 | 1_2_3|テスト_kong_123 | 1111 | NULL | NULL | NULL |+----+--------------+---------------+------+-------------+----------+---------+1 row in setmysql> UPDATE user set `user_id_tree`= replace( `user_id_tree`, '_2_3', '&2&3') where `id`=1;Query OK, 1 row affectedRows matched: 1 Changed: 1 Warnings: 0mysql> select * from user;+----+--------------+---------------+------+-------------+----------+---------+| ID | user_id_tree|nickname|NAME|DESCRIPTION|QUANTITY|created|+----+----+----+----+----+--------+------------+----------------------------------------------------------------------------------------------------------------kong_123 | 1111 | NULL | NULL | NULL |+----+--------------+---------------+------+-------------+----------+---------+1 row in setmysql> replace into user (name) value (222);Query OK, 1 row affectedmysql> select * from user;+----+--------------+---------------+------+-------------+----------+---------+| ID | user_id_tree|nickname|NAME|DESCRIPTION|QUANTITY|created|+----+----+----+----+----+--------+------------+----------------------------------------------------------------------------------------------------------------kong_123|1111|NULL|NULL|NULL|NULL|9|NULL|NULL|222|NULL|NULL|NULL|NULL|NULL|+----+----+----+----+----+----+----+----+----+----+2 rows in set 3、備考:mysqlヘルプコマンドは詳細なコマンド説明を見ることができます.helpまたは疑問符の使用
たとえばreplace関数の使用説明を表示します:help replaceまたは?replace
root@localhost : xxx_server 10:52:03>? replace;Name: 'REPLACE'Description:Syntax:REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [PARTITION (partition_name [, partition_name] ...)] [(col_name [, col_name] ...)] {VALUES | VALUE} (value_list) [, (value_list)] ...REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [PARTITION (partition_name [, partition_name] ...)] SET assignment_listREPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [PARTITION (partition_name [, partition_name] ...)] [(col_name [, col_name] ...)] SELECT ...value: {expr | DEFAULT}value_list: value [, value] ...assignment: col_name = valueassignment_list: assignment [, assignment] ...REPLACE works exactly like INSERT, except that if an old row in thetable has the same value as a new row for a PRIMARY KEY or a UNIQUEindex, the old row is deleted before the new row is inserted. See [HELPINSERT].REPLACE is a MySQL extension to the SQL standard. It either inserts, ordeletes and inserts. For another MySQL extension to standard SQL---thateither inserts or updates---seehttp://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html.DELAYED inserts and replaces were deprecated in MySQL 5.6. In MySQL5.7, DELAYED is not supported. The server recognizes but ignores theDELAYED keyword, handles the replace as a nondelayed replace, andgenerates an ER_WARN_LEGACY_SYNTAX_CONVERTED warning. ("REPLACE DELAYEDis no longer supported. The statement was converted to REPLACE.") TheDELAYED keyword will be removed in a future release.*Note*:REPLACE makes sense only if a table has a PRIMARY KEY or UNIQUE index.Otherwise, it becomes equivalent to INSERT, because there is no indexto be used to determine whether a new row duplicates another.Values for all columns are taken from the values specified in theREPLACE statement. Any missing columns are set to their default values,just as happens for INSERT. You cannot refer to values from the currentrow and use them in the new row. If you use an assignment such as SETcol_name = col_name + 1, the reference to the column name on the righthand side is treated as DEFAULT(col_name), so the assignment isequivalent to SET col_name = DEFAULT(col_name) + 1.To use REPLACE, you must have both the INSERT and DELETE privileges forthe table.If a generated column is replaced explicitly, the only permitted valueis DEFAULT. For information about generated columns, seehttp://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html.REPLACE supports explicit partition selection using the PARTITIONkeyword with a list of comma-separated names of partitions,subpartitions, or both. As with INSERT, if it is not possible to insertthe new row into any of these partitions or subpartitions, the REPLACEstatement fails with the error Found a row not matching the givenpartition set. For more information and examples, seehttp://dev.mysql.com/doc/refman/5.7/en/partitioning-selection.html.URL: http://dev.mysql.com/doc/refman/5.7/en/replace.html
転載先:https://www.cnblogs.com/xuzhujack/p/11540187.html
SELECT*FROM`user`where`nickname`LIKE'%テスト%'
SELECT*FROM`user`where`nickname`not LIKE'%テスト%'
2、mysql一括置換replace関数の使い方:
replaceはフィールド内の指定された部分mysql> select replace('12_34_56','_','&');+-----------------------------+| replace('12_34_56','_','&') |+-----------------------------+| 12&34&56 |+-----------------------------+
mysql> select * from user;+----+--------------+---------------+------+-------------+----------+---------+| ID | user_id_tree | nickname | NAME | DESCRIPTION | QUANTITY | created |+----+--------------+---------------+------+-------------+----------+---------+| 1 | 1_2_3|テスト_kong_123 | 1111 | NULL | NULL | NULL |+----+--------------+---------------+------+-------------+----------+---------+1 row in setmysql> UPDATE user set `user_id_tree`= replace( `user_id_tree`, '_2_3', '&2&3') where `id`=1;Query OK, 1 row affectedRows matched: 1 Changed: 1 Warnings: 0mysql> select * from user;+----+--------------+---------------+------+-------------+----------+---------+| ID | user_id_tree|nickname|NAME|DESCRIPTION|QUANTITY|created|+----+----+----+----+----+--------+------------+----------------------------------------------------------------------------------------------------------------kong_123 | 1111 | NULL | NULL | NULL |+----+--------------+---------------+------+-------------+----------+---------+1 row in setmysql> replace into user (name) value (222);Query OK, 1 row affectedmysql> select * from user;+----+--------------+---------------+------+-------------+----------+---------+| ID | user_id_tree|nickname|NAME|DESCRIPTION|QUANTITY|created|+----+----+----+----+----+--------+------------+----------------------------------------------------------------------------------------------------------------kong_123|1111|NULL|NULL|NULL|NULL|9|NULL|NULL|222|NULL|NULL|NULL|NULL|NULL|+----+----+----+----+----+----+----+----+----+----+2 rows in set 3、備考:mysqlヘルプコマンドは詳細なコマンド説明を見ることができます.helpまたは疑問符の使用
たとえばreplace関数の使用説明を表示します:help replaceまたは?replace
root@localhost : xxx_server 10:52:03>? replace;Name: 'REPLACE'Description:Syntax:REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [PARTITION (partition_name [, partition_name] ...)] [(col_name [, col_name] ...)] {VALUES | VALUE} (value_list) [, (value_list)] ...REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [PARTITION (partition_name [, partition_name] ...)] SET assignment_listREPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [PARTITION (partition_name [, partition_name] ...)] [(col_name [, col_name] ...)] SELECT ...value: {expr | DEFAULT}value_list: value [, value] ...assignment: col_name = valueassignment_list: assignment [, assignment] ...REPLACE works exactly like INSERT, except that if an old row in thetable has the same value as a new row for a PRIMARY KEY or a UNIQUEindex, the old row is deleted before the new row is inserted. See [HELPINSERT].REPLACE is a MySQL extension to the SQL standard. It either inserts, ordeletes and inserts. For another MySQL extension to standard SQL---thateither inserts or updates---seehttp://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html.DELAYED inserts and replaces were deprecated in MySQL 5.6. In MySQL5.7, DELAYED is not supported. The server recognizes but ignores theDELAYED keyword, handles the replace as a nondelayed replace, andgenerates an ER_WARN_LEGACY_SYNTAX_CONVERTED warning. ("REPLACE DELAYEDis no longer supported. The statement was converted to REPLACE.") TheDELAYED keyword will be removed in a future release.*Note*:REPLACE makes sense only if a table has a PRIMARY KEY or UNIQUE index.Otherwise, it becomes equivalent to INSERT, because there is no indexto be used to determine whether a new row duplicates another.Values for all columns are taken from the values specified in theREPLACE statement. Any missing columns are set to their default values,just as happens for INSERT. You cannot refer to values from the currentrow and use them in the new row. If you use an assignment such as SETcol_name = col_name + 1, the reference to the column name on the righthand side is treated as DEFAULT(col_name), so the assignment isequivalent to SET col_name = DEFAULT(col_name) + 1.To use REPLACE, you must have both the INSERT and DELETE privileges forthe table.If a generated column is replaced explicitly, the only permitted valueis DEFAULT. For information about generated columns, seehttp://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html.REPLACE supports explicit partition selection using the PARTITIONkeyword with a list of comma-separated names of partitions,subpartitions, or both. As with INSERT, if it is not possible to insertthe new row into any of these partitions or subpartitions, the REPLACEstatement fails with the error Found a row not matching the givenpartition set. For more information and examples, seehttp://dev.mysql.com/doc/refman/5.7/en/partitioning-selection.html.URL: http://dev.mysql.com/doc/refman/5.7/en/replace.html
転載先:https://www.cnblogs.com/xuzhujack/p/11540187.html