. .

6.6: MySQL

» PHP » MySQL » MySQL

NULL, , max_allowed_packet.

, , 1, 0.


ASCII(str)

ASCII- str. 0, str . NULL, str NULL:

mysql> select ASCII("2");
    -> 50.
mysql> select ASCII(2);
    ->50
mysql> select ASCII("dx");
    -> 100

ORD(str)

str , , ASCII- : (( ASCII-)*256 + ( ASCII-))[*256 + ASCII- +...]. , ASCII():

mysql> select ORD("2");
    -> 50

(N, from_base, to_base).

. N, from_base to_base. NULL, NULL.

N , . - 2, - 36. to_base - , N . N . CONV 64- :

mysql> select CONV(a,16,2);
    -> 1010"
mysql> select CONV(6E,18,8);
    -> "172"
mysql> select C0NV(-17,10,-18);
    -> "-H"
mysql> select CONV(10+10"+'10'+0xa,10,10);
    -> "40"

BIN(N)

N, N longlong (BIGINT). CONV(N,10,2). NULL, N NULL:

mysql> select BIN(12);
    -> 1100"

OCT(N)

N, N longlong (BIGINT). C0NV(N,10,8). NULL, N NULL:

mysql> select OCT(12);
    -> "14"

HEX(N)

N, N longlong (BIGINT). CONV (N,10,16). NULL, N NULL:

mysql> select (255);
    -> "FF"

CHAR(N,. . .).

CHAR() , , ASCII- . NULL :

mysql> select CHAR(77,121,83,81,'76');
    -> "MySQL"
mysql> select CHAR(77,77.3,'77.3');
    -> ""

CONCAT(str1,str2,...).

, . NULL, NULL. , 2 . :

mysql> select CONCAT ("My", "S", QL");
    -> "MySQL"
mysql> select CONCAT ("My", NULL, "QL");
    -> NULL
mysql> select CONCAT(14.3);
    -> '14.3'

CONCAT_WS(separator, str1, str2,...).

CONCAT_WS() CONCAT With Separator ( ) CONCAT(). . , . NULL, NULL. , :

mysql> select CONCAT_WS(,, First name, Second name, Last Name);
    -> "First name,Second name,Last Name"
mysql> select CONCAT_WS(,,First name,NULL,Last Name);
    -> "First name,Last Name"

LENGTH (str), OCTET_LENGTH(str), CHAR_LENGTH(str) CHARACTER_LENGTH(str)

str:

mysql> select LENGTH("text");
    -> 4
mysql> select OCTET_LENGTH("text");
    -> 4

, CHAR_LENGTH() .


LOCATE(substr,str), POSITION(substr IN str), LOCATE(substr,str) POSITION(substr IN str)

substr str. substr str, 0:

mysql> select LOCATE("bar", "foobarbar");
    -> 4
mysql> select LOCATE("xbar", "foobar");
    -> 0

.


LOCATE(substr,str,pos).

substr str, pos. substr str, 0:

mysql> select LOCATE("bar", "foobarbar",5);
    -> 7

.


INSTR(str,substr)

LOCATE(), :

mysql> select INSTR("foobarbar", "bar");
    -> 4
mysql> select INSTR("xbar", "foobar");
    -> 0

.


LPAD(str,len,padstr)

str, padstr. str len . str , len, len . str , len , padstr .

mysql> select LPAD("hi",4,'??');
    -> "??hi"

RPAD (str,len,padstr)

LPAD(str,len,padstr), padstr .

mysql> select RPAD("hi",5,'?');
    -> "hi???"

LEFT(str,len)

len str:

mysql> select LEFT(foobarbar", 5);
    -> "fooba"

.


RIGHT(str,len)

len str:

mysql> select RIGHT("foobarbar", 4);
    -> "rbar"

.


SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len) MID(str,pos,len)

len str, pos. , FROM ANSI SQL92:

mysql> select SUBSTRING("Quadratically",5,6);
    -> "ratica"

.


SUBSTRING(str,pos) SUBSTRING(str FROM pos)

str, pos:

mysql> select SUBSTRING("Quadratically",5);
    -> "ratically"
mysql> select SUBSTRING("foobarbar" FROM 4);
    -> "barbar"

.


SUBSTRING_INDEX(str,delim,count)

str, count delim. count , . count , :

mysql> select SUBSTRING_INDEX("www.mysql.com", ".", 2);
    -> "www.mysql"
mysql> select SUBSTRING_INDEX(www.mysql.com", ".", -2);
    -> "mysql.com"

.


LTRIM(str)

str :

mysql> select LTRIM(" barbar");
    -> "barbar"

RTRIM(str)

str :

mysql> select RTRIM("barbar ");
    -> "barbar"

.


TRIM([[BOTH|LEADING|TRAILING] [remstr] FROM] str)

str, remstr. BOTH, LEADING TRAILING , BOTH. remstr, . LEADING , TRAILING , a BOTH :

mysql> select TRIM(" bar ");
    -> "bar"
mysql> select TRIM(LEADING "x" FROM "xxxbarxxx");
    -> "barxxx'
mysql> select TRIM(BOTH "x" FROM "xxxbarxxx");
    -> "bar"
mysql> select TRIM(TRAILING "xyz" FROM "barxxyz");
    -> "barx"

.


SOUNDEX(str)

soundex- str. , . , , soundex-.

soundex- 4 , SOUNDEX() . SUBSTRING() , soundex-. - . A-Z :

mysql> select SOUNDEX("Hello");
    -> "H400"
mysql> select SOUNDEX("Quadratically");
    -> "Q36324"

SPACE(N)

, N-:

mysql> select SPACE(6);
    -> "      "

REPLACE(str, from_str, to_str)

str, from_str to_str:

mysql> select REPLACE("www.mysql.com", "w", "Ww");
    -> "WwWwWw.mysql.com"

.


REPEAT(str, count)

, str, count . count <=0, . str count NULL, NULL:

mysql> select REPEAT("MySQL", 3);
    -> "MySQLMySQLMySQL"

REVERSE(Str)

str :

mysql> select REVERSE("abc");
    -> "cba"

.


INSERT(str, pos, len, newstr)

str , pos len , newstr:

mysql> select INSERT("Quadratic", 3, 4, "What");
    -> "QuWhattic"

.


ELT(N,str1,str2,str3,...)

str1, N=1, str2, N=2 . NULL, N , 1, , . ELT() FIELD(), :

mysql> select ELT(1, "ej", "Heja", "hej", "foo");
    -> "ej"
mysql> select ELT(4, "ej", "Heja", "hej", "foo");
    -> "foo"

FIELD(str,str1,str2,str3,...)

str str1, str2, str3 0, str . FIELD() ELT():

mysql> select FIELD("ej", "Hej", "ej", "Heja", "hej", "foo");
    -> 2
mysql> select FIELD("fo", "Hej", "ej", "Heja", "hej", "foo");
    -> 0

FIND_IN_SET(str,strlist)

1 N, str strlist, N . , , (,).

, SET, FIND_IN_SET() , . 0, str strlist strlist . NULL, NULL. , :

mysql> SELECT FIND_IN_SET(b",'a,b,c,d');
    -> 2

MAKE_SET (bits, str1 str2, ...)

, , (,), , bits.str1 0, str2 1 . NULL str1, str2, ... :

mysql> SELECT MAKE_SET(1,'','b','');
    -> ""
mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');
    -> "hello,world"
mysql> SELECT MAKE_SET(0,'a','b','c');
    -> ""

EXPORT_SET(bits, on, off, [separator, [number_of_bits]])

, , bit, , on, , , , off. ( separator, ) number_of_bits ( 64):

mysql> select EXPORT_SET(5,'','N',',,4)
    -> Y,N,Y,N

LCASE(Str) LOWER(str)

str , ( : ISO-8859-1 Latin1):

mysql> select LCASE("QUADRATICALLY");
    -> "quadratically"

.


UCASE(str) UPPER(str)

str , ( : ISO-8859-1 Latin1):

mysql> select UCASE("Hej");
    -> "HEJ"

.


LOAD_FILE(file_name)

. ( !), file ( , , ). , max_allowed_packet. , NULL:

mysql> UPDATE table_name SET blob_column=LOAD_FILE(/tmp/picture) WHERE id=1;

MySQL :

mysql> SELECT 1+1";
    -> 2
mysql> SELECT CONCAT(2,' test);
    -> 2 test

, CONCAT().

, . , , . .