![]() ![]() In the long run, I'm not sure that I can think of a great use-case for GROUP_CONCAT() and these limitations definitely strengthen that perception. But, I figured I'd share this since we were banging our heads against the wall for a while trying to figure out what was going on. It's easy enough to refactor the algorithms so that they don't use GROUP_CONCAT(). And, as you can imagine, if the truncation happens mid-transactionID, we're not just dealing with missing data, we're now dealing with false data. Instead of throwing a SQL error, it just truncated the value. Ultimately, though, the real problem is not that GROUP_CONCAT() has a limit, it's that it failed silently. The problem is that GROUP_CONCAT() has a native default limit of 1,024 characters (which is further constrained by the maximum size of the packet that MySQL can return). ![]() Here, each day of the week is reporting only 147 transactions, not the full 1,000 that we inserted. And yet, when we run the above code and output the report, we get the following: n|( n256.GROUP_CONCAT( transactionID ) AS transactionListĭay Of Week (#report.dayOfWeek#): #listLen( ansactionList )#Īs you can see from the INSERT statements, each day of the week should have 1,000 transaction IDs associated with it. If you really insist, though, you can fake the direct join by cross-matching with FINDINSET (which conveniently expects a comma-delimited string of items). n << 8), split_string_into_rows( NULL)) AS e Limited to 65535 results (slightly slower)ĬREATE OR REPLACE VIEW split_string_into_rows(i,e) AS n < SET_COUNT(split_string_into_rows( NULL))) SELECT HIGH_PRIORITY SQL_SMALL_RESULT n1. This preforms much faster than FIND_IN_SET() because the indexes are preserved.ĬREATE OR REPLACE ALGORITHM = MERGE VIEW split_string_into_rows(i,e) AS Field indexes are still used when doing a join against a string split! SELECT STRAIGHT_JOIN u.name FROM split_string_into_rows s, users u WHERE u.birth_month = s.e AND split_string_into_rows('March,April,May,June') SELECT u.name FROM users u JOIN split_string_into_rows s ON u.birth_month = s.e WHERE split_string_into_rows('March,April,May') ![]() SELECT e FROM split_string_into_rows WHERE split_string_into_rows('foo,bar,foobar,barfoo') SET = 'foo,bar,foobar' SELECT e FROM split_string_into_rows The comma-separated string is passed via Usage Examples: Resultset sorted by index, starting at zero Result set contains the index (`i`) and element (`e`) Splits a comma-delimited string (aka csv aka comma-separated string) into rows RETURN IF($split_string_into_rows IS NULL, ' '), '1 =$split_string_into_rows) Only used as a work-around to pass to the split_string_into_rows VIEWĬREATE FUNCTION split_string_into_rows($split_string_into_rows MEDIUMBLOB) RETURNS MEDIUMBLOB ( SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) n4 ( SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) n3, ( SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) n2, ( SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) n1, Insert numbers 0-255 into the number_set table I recommend adding this INSERT query below to the mysql -init-file, if using MEMORY engine Note: If using MEMORY engine for the number_set table, the data in MEMORY tables is lost on server restart, ) ENGINE =INNODB DEFAULT CHARSET =BINARY MAX_ROWS = 256 MIN_ROWS = 256 `n ` TINYINT( 3) UNSIGNED NOT NULL PRIMARY KEY, The ENGINE=MEMORY engine may be used for a performance gain, but see note on the MEMORY engine listed below This is a handy tool to pivot a table (or mysql result) row of columns into a column of rows A column of integers counting from 0 to 255 RETURN 1 +CHAR_LENGTH($strlist) -CHAR_LENGTH(REPLACE($strlist, ', ', ' ')) (Actually returns the one plus the number of commas in the string)ĬREATE FUNCTION SET_COUNT($strlist MEDIUMBLOB) RETURNS SMALLINT UNSIGNED Returns the number of elements in a set Usage Example: SELECT SET_EXTRACT(2,'foo,bar,foobar') // "foobar"ĬREATE FUNCTION SET_EXTRACT($i SMALLINT UNSIGNED, $strlist MEDIUMBLOB) RETURNS VARBINARY( 255) If either parameter is NULL or if $i is outside the element count, NULL will be returned If index $i is negative, the elements are instead counted from the right, starting at -1. If index $i is zero or positive, the elements are counted from the left, starting at zero. Splits a comma-separated string (AKA "SET"), $strlist, and returns the element (aka substring) matching the provided index, $i. Essentially does the reverse of MySQL's built-in function FIND_IN_SET(str,strlist) = index INT (four occurrences in SET_EXTRACT and one occurrence in SET_COUNT) Just change all the occurrences of ',' to the new delimiter This is designed to work with a comma-separated string (csv, SET, array) Split a string into a mysql resultset of rows ![]()
0 Comments
Leave a Reply. |