進(jìn)階高級(jí)特級(jí)特性

高級(jí)特性字符集character

setcollationselect

*

from

information_schema.character_setsselect*

from

collations

where

character_set_name

='utf8';variablescharacter_set_system

=

utf8character_set_servercharacter_set_databasecharacter_set_connectioncharacter_set_resultscharacter_set_clientvariablescollation_connectioncollation_databasecollation_server字符串的編碼stored

program存儲(chǔ)過程(stored

procedure)函數(shù)(function)觸發(fā)器(trigger)事件調(diào)度器(event)show

create

…stored

proceduredrop

procedure

if

exists

pro_create_tab;delimiter

//create

procedure

pro_create_tab()BEGINset

@i=0;while

@i<=99

doset

@sqlstr=concat('CREATE

TABLEuser_',lpad(@i,2,0),'(id

bigint

NOT

NULL

AUTO_INCREMENT,user

varchar(20),gender

char(1),PRIMARY

KEY

(ID))');prepare

stmt

from

@sqlstr;execute

stmt;set

@i=@i+1;end

while;END

//delimiter

;call

pro_create_tab();functiondelimiter

//drop

function

if

exists

bphit;create

function

bphit

()

returns

floatDETERMINISTICbeginselect

variable_value

into

@read

from

information_schema.global_status

where

variable_name

=

'iselect

variable_value

into

@req_read

from

information_schema.global_status

where

variable_namreturn

(@req_read-@read)/@req_read;end//delimiter

;triggerbefore/afterinsert/update/delete5.7delimiter

//drop

trigger

if

exists

trg_after_insert_user

//create

trigger

trg_after_insert_user

after

insert

on

user_00for

each

rowbegininsert

into

counter

(gender,

c)

values

(new.gender,

1)

onduplicate

key

update

c=c+1;end//delimiter

;delimiter

//drop

triggerifexists

trg_after_insert_user_all

//create

trigger

trg_after_insert_user_all

after

insert

on

userfor

each

rowbeginset

@prefix

=

new.id

%

100;case

@prefixwhen

0

then

insert

into

user_00

values

(new.id,

new.user,

new.gender);when

1

then

insert

into

user_01

values

(new.id,

new.user,

new.gender);when

2

then

insert

into

user_02

values

(new.id,

new.user,

new.gender);elsebeginend;end

case;end//delimiter

;Insert

into

user_88

select

*

from

user

where

id

%

100=

88;online

schema

change?eventdelimiter//drop

event

e_store_ts

//create

event

e_store_tson

scheduleevery

10

seconddobegininsert

into

ts

values

(now());insert

into

ts

values

(now());end//delimiter

;delimiter//drop

event

e_store_at

//create

event

e_store_aton

schedule

at

‘2016-02-20

21:07:00’dobegininsert

into

ts

values

(now());insert

into

ts

values

(now());end//delimiter

;視圖create

view

v

as

select

xxxalgorithm

=

merge|temptable|undefinedwith

check

optionview

vs.

temporary

tablegrant

for

rows

and

column全文索引InnoDB/MyISAMchar/varchar/textalter

table

xxx

add

fulltext

index

(col)select

*

from

xxx

where

match(col)

against

(key)in

natural

language

modein

natural

language

mode

with

query

expansionin

boolean

modewith

query

expansionSelect

xx

from

xx

where

match(content)

against(‘database*’

in

Boolean

mode)partitionrange/list/columns/hash/key/primary

key5.6

報(bào)錯(cuò)信息globalrangecreate

table

test_p(id

int,dt

datetime

not

null)partition

by

range

(to_days(dt))(partition

p0

values

less

than

(to_days('2016-01-01')),partition

p1

values

less

than

(to_days('2016-02-01')),partition

p2

values

less

than

(to_days('2016-03-01')),partition

p3

values

less

than

maxvalue);insert

into

test_p

select

rand()*100000000,

'2016-1-1'+interval

rand()*200

day

from

test_p;auditinit-connect='insert

into

audit.login_log(ConnectionID,ConnUserName,PrivMatchName,LoginTime)values(connection_id(),user(),