Jlin's Blog

LEFT(str,len)

Returns the leftmost len characters from the string str, orNULLif any argument isNULL.

1
2

mysql> SELECT LEFT('foobarbar', 5);
        -> 'fooba'

This function is multibyte safe.

问题：生产环境sql执行很慢

分析sql执行

t_user_user表并没有使用到索引

GROUP_CONCAT(expr)

This function returns a string result with the concatenated non-NULLvalues from a group. It returnsNULLif there are no non-NULLvalues. The full syntax is as follows:

1
2
3
4

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

1
2
3
4

mysql> SELECT student_name,
         GROUP_CONCAT(test_score)
       FROM student
       GROUP BY student_name;

Or:

1
2
3
4
5

mysql> SELECT student_name,
         GROUP_CONCAT(DISTINCT test_score
                      ORDER BY test_score DESC SEPARATOR ' ')
       FROM student
       GROUP BY student_name;

In MySQL, you can get the concatenated values of expression combinations. To eliminate duplicate values, use theDISTINCTclause. To sort values in the result, use theORDER BYclause. To sort in reverse order, add theDESC(descending) keyword to the name of the column you are sorting by in theORDER BYclause. The default is ascending order; this may be specified explicitly using theASCkeyword. The default separator between values in a group is comma (,). To specify a separator explicitly, useSEPARATORfollowed by the string literal value that should be inserted between group values. To eliminate the separator altogether, specifySEPARATOR ''.

The result is truncated to the maximum length that is given by thegroup_concat_max_lensystem variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value ofmax_allowed_packet. The syntax to change the value ofgroup_concat_max_lenat runtime is as follows, where*val*is an unsigned integer:

1

SET [GLOBAL | SESSION] group_concat_max_len = val;

The return value is a nonbinary or binary string, depending on whether the arguments are nonbinary or binary strings. The result type isTEXTorBLOBunlessgroup_concat_max_lenis less than or equal to 512, in which case the result type isVARCHARorVARBINARY.

查看死锁信息

DATE_FORMAT(date,format)

Formats the date value according to the format string.

The following specifiers may be used in the format string. The % character is required before format specifier characters.