![]() Clearly, what we wanted was the actual text. The cause of the problem is that the regular expression has been interpreted as a search for any entries that read 'CD-RW Model' followed by either a 7, 5, 4 or 3. If we were to search for this without regard to the fact that the prod_name value contains regular expression special characters we will not get what we wanted: mysql> SELECT * FROM product WHERE prod_name REGEXP 'CD-RW Model ' For example, suppose we have a row in our product table which reads as follows: +-+-+-+ In SQL, escaping involves preceding any characters that may be misinterpreted as a regular expression special character with double back slashes (\\). To address this issue, a concept known as escaping is used. Obviously if you are are looking for text that looks like a regular expression, the text for which you want to search is, itself, going to be viewed as regular expression syntax. Those who studied critical thinking at college will already be questioning what to do if the character sequence that is the subject of a search contains one or more of these characters. For example the dot (.) and square brackets () all have special meaning. ![]() There is no limit to the number of characters that can be grouped in the brackets when using this filtering technique.Īs you have seen, regular expressions assign special meaning to particular characters. Use of this syntax ensures that only the words 'Grey' and 'Gray will match the search criteria. For example: mysql> SELECT * FROM product WHERE prod_name REGEXP 'Gry Computer Case' The syntax for this requires that the characters be places in square brackets at the desired location in the match text. Fortunately, regular expressions allow us to specify a group of acceptable character matches for any character position. In reality we are only interested in words that contain either an 'a' or an 'e' in that location. One problem with the approach outlined above is that any letter between the 'Gr' and the 'y' would have registered as a match. Regular expressions, however, do not stop here. So far we haven't done anything that could not have been achieved using wildcards. | 12 | Gray Computer Case | ATX PC CASE (USA) | | 11 | Grey Computer Case | ATX PC CASE | For example: mysql> SELECT * FROM product WHERE prod_name REGEXP 'Gr.y Computer Case' Rather like the LIKE underscore wildcard, this character indicates that any character in this position in the text will be considered a match. To perform this retrieval we will use the regex dot character matching (.). As in the previous chapter we need to retrieve rows from a table taking into consideration the difference in spelling of the color gray (grey). select * from a FULL OUTER JOIN b on a.a = b.In order to introduce the REGEXP operator, we will begin by looking at a use of regular expressions that could similarly be used with the LIKE operator. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa. Select a.*, b.* from a,b where a.a(+) = b.b Ī full outer join will give you the union of A and B, i.e. select * from a RIGHT OUTER JOIN b on a.a = b.b Select a.*, b.* from a,b where a.a = b.b(+) Ī right outer join will give all rows in B, plus any common rows in A. select * from a LEFT OUTER JOIN b on a.a = b.b Select a.*, b.* from a,b where a.a = b.b Ī left outer join will give all rows in A, plus any common rows in B. select * from a INNER JOIN b on a.a = b.b Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.Īn inner join using either of the equivalent queries gives the intersection of the two tables, i.e. Suppose you have two tables, with a single column each, and data as follows: A B the inner part of a Venn diagram intersection.Īn outer join of A and B gives the results of A union B, i.e. The performance of like when using an index is very close to = (assuming the same number of rows returned).Īssuming you're joining on columns with no duplicates, which is a very common case:Īn inner join of A and B gives the result of A intersect B, i.e. SELECT * FROM t WHERE a LIKE '_agf' <<- cannot use an index ![]() SELECT * FROM t WHERE a LIKE '%a%' <<- cannot use an index SELECT * FROM t WHERE a LIKE 'a%' <<- can use an index, depending on cardinality SELECT * FROM t WHERE a LIKE 'abc%' <<- can use an index SELECT * FROM t WHERE a LIKE 'abc' <<- (case insensitive `=`) can use an index LIKE can use an index if the wildcard is not the first char. No other indexes on the same table are better suited (MySQL can only use one index per table per subselect) Ĭonsidering these and some other more esoteric caveats an = comparison is much faster than a regexp.the values in the column have sufficient cardinality (if more than +/- 20% of the rows match, MySQL will not use an index, because in that case doing a full table scan is faster).The regexp can never use an index in MySQL.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |