Mysql triggers, fuzzy search, stored procedures, built-in functions

Source: Internet
Author: User
Tags decimal to binary
Mysql triggers, fuzzy searches, stored procedures, and built-in functions originally thought that some of Mysql's knowledge was almost the same, but I found that I forgot everything when I used it in projects. Now let's review it and take a note.


  • View all triggers
    SELECT * FROM information_schema.`TRIGGERS`;
  • Trigger application

    Background: two tables:

    Create a trigger to insert data in yyd_table. if yyd _ is used, data insertion is also triggered.


    Execution result:

    NextWhen inserting yyd_name, change the name to 4:

    CREATE TRIGGER yyd_tri1 BEFORE INSERT ON yyd_name FOR EACH ROWBEGINSET @x = "123321";SET = "4";END;
  • Delete trigger
    DROP triggeryyd_tri;
  • Possible problems

    If you insert/update the data you just inserted in the trigger, it will cause a loop call. For example:

    create trigger test before update on test for each row
    update test set NEW.updateTime = NOW() where id=NEW.ID;

    Set should be used:

    create trigger test before update on test for each row set NEW.updateTime = NOW(); END
  • Syntax

    CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt

    Trigger_time is the time when the program is triggered. It can be BEFORE or AFTER

    Trigger_event indicates the type of statements used to activate the trigger program. Trigger_event can be one of the following values:

  • INSERT: The Trigger program is activated when a new row is inserted into the table, for example, through INSERT, load data, and REPLACE statements.
  • UPDATE: The Trigger program is activated when a row is changed, for example, through the UPDATE statement.
  • DELETE: The Trigger program is activated when a row is deleted from the table, for example, through the DELETE and REPLACE statements.

    Fuzzy search

    SELECT field FROM table WHERE a field Like condition
  • Matching mode
  • %

    : Represents any 0 or multiple characters. It can match characters of any type and length.

    SELECT * FROM [user] WHERE u_name LIKE '% 3%'

    We will find all records with "3", such as "3 Zhang", "3 Zhang", "3 Zhang Mao", and "3 Tang Sanzang.

    In addition, if you need to find records with "three" and "cat" in u_name, use the and condition.

    SELECT * FROM [user] WHERE u_name LIKE '% 3%' AND u_name LIKE '% cat %'
  • []: Represents one of the characters listed in parentheses (similar to regular expressions ). Specifies a character, string, or range. the matching object must be one of them.
  • [^]: Represents a single character not listed in parentheses. The value is the same as [], but it requires that the matched object be any character other than the specified character.
  • _

    : Represents any single character. Matches any character. it is often used to limit the character length of an expression.

    SELECT * FROM [user] WHERE u_name LIKE '_ 3 _'

    Only find that the u_name such as "Tang Sanzang" is three characters and the middle word is "three;

    SELECT * FROM [user] WHERE u_name LIKE 'three __';

    Find out that the name of the "three-legged cat" is three characters and the first word is "three;

    Stored Procedure

  • Format
    mysql> DELIMITER //mysql> CREATE PROCEDURE proc1(OUT s int)-> BEGIN -> SELECT COUNT(*) INTO s FROM user;-> END -> //mysql> DELIMITER ;
  • Here we need to note that DELIMITER // and DELIMITER; are two sentences, DELIMITER is the meaning of the DELIMITER, here the DELIMITER is replaced //.
  • The stored procedure may have input, output, and input and output parameters as needed. here, there is an output parameter s, whose type is int type. if there are multiple parameters, separate them.
  • Start and END of the program are identified by BEGIN and END.

    MySQL Stored Procedure parameters are used IN the definition of stored procedures. There are three parameter types: IN, OUT, and INOUT. the format is as follows:

    Create procedure ([[IN | OUT | INOUT] parameter name data class...])

    IN Input parameters:Indicates that the value of this parameter must be specified when the stored procedure is called. modifying the value of this parameter in the stored procedure cannot be returned, which is the default value.

    OUT Output parameters:This value can be changed within the stored procedure and can be returned

    INOUT Input and output parameters:Can be changed and returned.

    For example:

    mysql > DELIMITER //mysql > CREATE PROCEDURE demo_in_parameter(IN p_in int)-> BEGIN -> SELECT p_in; -> SET p_in=2; -> SELECT p_in; -> END; -> //mysql > DELIMITER ;


    mysql > SET @p_in=1;mysql > CALL demo_in_parameter(@p_in);+------+| p_in |+------+| 1| +------+ +------+| p_in |+------+| 2| +------+ mysql> SELECT @p_in;+-------+| @p_in |+-------+|1|+-------+
  • Variable
    DECLARE variable_name [,variable_name...] datatype [DEFAULT value];

    Datatype is the data type of MySQL, such as int, float, date, varchar (length)

    For example:

    DECLARE l_int int unsigned default 4000000;DECLARE l_numeric number(8,2) DEFAULT 9.95;DECLARE l_date date DEFAULT '1999-12-31';DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';


    SET variable name = expression value [, variable_name = expression...]

    User variable:

    mysql > SELECT 'Hello World' into @x;mysql > SELECT @x;+-------------+| @x|+-------------+| Hello World |+-------------+mysql > SET @y='Goodbye Cruel World';mysql > SELECT @y;+---------------------+| @y|+---------------------+| Goodbye Cruel World |+---------------------+ mysql > SET @z=1+2+3;mysql > SELECT @z;+------+| @z |+------+|6 |+------+

    Variables and stored procedures:

    mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');mysql > SET @greeting='Hello';mysql > CALL GreetWorld( );+----------------------------+| CONCAT(@greeting,' World') |+----------------------------+|Hello World |+----------------------------+mysql> CREATE PROCEDURE p1() SET @last_procedure='p1';mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_proc);mysql> CALL p1( );mysql> CALL p2( );+-----------------------------------------------+| CONCAT('Last procedure was ',@last_proc|+-----------------------------------------------+| Last procedure was p1 |+-----------------------------------------------+
  • Variable scope:

    mysql > DELIMITER //mysql > CREATE PROCEDURE proc3()-> begin -> declare x1 varchar(5) default 'outer';-> begin -> declare x1 varchar(5) default 'inner';-> select x1;-> end;-> select x1;-> end;-> //mysql > DELIMITER ;

    Internal variables have a higher priority in the scope of their scopes. when the execution ends, the internal variables disappear and are out of the scope, and the variables are no longer visible, the declarative variable cannot be found no longer outside the stored procedure, but you can save it by using the out parameter or assigning its value to the session variable.

  • Query stored procedures
  • Select name from mysql. proc where db = 'database name'; select routine_name from information_schema.routines where routine_schema = 'database name'; show procedure status where db = 'database name ';
    • Delete stored procedure
    • If-then-else Statement
    mysql > DELIMITER //mysql > CREATE PROCEDURE proc2(IN parameter int)-> begin -> declare var int;-> set var=parameter+1;-> if var=0 then -> insert into t values(17);-> end if;-> if parameter=0 then -> update t set s1=s1+1;-> else -> update t set s1=s1+2;-> end if;-> end;-> //mysql > DELIMITER ;
    • Case Statement
    mysql > DELIMITER //mysql > CREATE PROCEDURE proc3 (in parameter int)-> begin -> declare var int;-> set var=parameter+1;-> case var-> when 0 then -> insert into t values(17);-> when 1 then -> insert into t values(18);-> else -> insert into t values(19);-> end case;-> end;-> //mysql > DELIMITER ;
    • While... end while statement
    mysql > DELIMITER //mysql > CREATE PROCEDURE proc4()-> begin -> declare var int;-> set var=0;-> while var<6 do-> insert into t values(var);-> set var=var+1;-> end while;-> end;-> //mysql > DELIMITER ;
    • Repeat · end repeat statement
    mysql > DELIMITER //mysql > CREATE PROCEDURE proc5 ()-> begin -> declare v int;-> set v=0;-> repeat-> insert into t values(v);-> set v=v+1;-> until v>=5-> end repeat;-> end;-> //mysql > DELIMITER ;

    Similar to do... While statement.

    • Loop... end loop statement
    mysql > DELIMITER //mysql > CREATE PROCEDURE proc6 ()-> begin -> declare v int;-> set v=0;-> LOOP_LABLE:loop-> insert into t values(v);-> set v=v+1;-> if v >=5 then -> leave LOOP_LABLE;-> end if;-> end loop;-> end;-> //mysql > DELIMITER ;

    The loop does not require the initial conditions. This is similar to the while loop, and does not require the end condition like the repeat loop. the leave statement is used to exit the loop.

    • ITERATE Iteration
    mysql > DELIMITER //mysql > CREATE PROCEDURE proc10 ()-> begin -> declare v int;-> set v=0;-> LOOP_LABLE:loop-> if v=3 then -> set v=v+1;-> ITERATE LOOP_LABLE;-> end if;-> insert into t values(v);-> set v=v+1;-> if v>=5 then -> leave LOOP_LABLE;-> end if;-> end loop;-> end;-> //mysql > DELIMITER ;

    You can refer to the compound statement label to start a compound statement.

    Built-in functions

    • String

    CHARSET (str) // returns the string character set

    CONCAT (string2 [,...]) // connection string

    INSTR (string, substring) // returns the position of the first occurrence of the substring in the string. If no position exists, 0 is returned.

    LCASE (string2) // converts it to lowercase

    LEFT (string2, length) // take the length from the LEFT of string2

    LENGTH (string) // string LENGTH

    LOAD_FILE (file_name) // read content from the file

    LOCATE (substring, string [, start_position]) is the same as INSTR, but the start position can be specified.

    LPAD (string2, length, pad) // repeat pad to start with string until the string length is length

    LTRIM (string2) // Remove leading spaces

    REPEAT (string2, count) // REPEAT count times

    REPLACE (str, search_str, replace_str) // REPLACE search_str with replace_str in str

    RPAD (string2, length, pad) // use pad after str until the length is length.

    RTRIM (string2) // remove backend spaces

    STRCMP (string1, string2) // compare the size of two strings by character,

    SUBSTRING (str, position [, length]) // starts from the position of str and takes length characters,

    Note: when processing strings in mysql, the Default subscript of the first character is 1, that is, the parameter position must be greater than or equal to 1.

    TRIM ([[BOTH | LEADING | TRAILING] [padding] FROM] string2) // remove the specified character FROM the specified position

    UCASE (string2) // converts to uppercase

    RIGHT (string2, length) // gets the last length character of string2

    SPACE (count) // Generate count spaces

    • Digital Mathematics

    ABS (number2) // absolute value

    BIN (decimal_number) // Convert decimal to binary

    CEILING (number2) // rounded up

    CONV (number2, from_base, to_base) // hexadecimal conversion

    FLOOR (number2) // round down

    FORMAT (number, decimal_places) // number of reserved decimal places

    HEX (DecimalNumber) // Convert to hexadecimal

    Note: HEX () can be passed in a string, return its ASC-11 code, such as HEX ('def ') returns 4142143 can also be passed in a decimal integer, return its hexadecimal encoding, for example, HEX (25) returns 19

    LEAST (number, number2 [,...]) // calculates the minimum value.

    MOD (numerator, denominator) // Evaluate the remainder

    POWER (number, power) // exponent

    RAND ([seed]) // random number

    ROUND (number [, decimals]) // rounding, decimals is the number of decimal places]

    • Date and time

    ADDTIME (date2, time_interval) // add time_interval to date2

    CONVERT_TZ (datetime2, fromTZ, toTZ) // Convert the time zone

    CURRENT_DATE () // Current date

    CURRENT_TIME () // current time

    CURRENT_TIMESTAMP () // Current timestamp

    DATE (datetime) // return the DATE part of datetime

    DATE_ADD (date2, INTERVAL d_value d_type) // add a date or time in date2

    DATE_FORMAT (datetime, FormatCodes) // Display datetime in formatcodes format

    DATE_SUB (date2, INTERVAL d_value d_type) // subtract a time from date2

    DATEDIFF (date1, date2) // Two date differences

    DAY (date) // returns the DAY of the date

    DAYNAME (date) // English week

    DAYOFWEEK (date) // Week (1-7), 1 is Sunday

    DAYOFYEAR (date) // The Day of the year

    EXTRACT (interval_name FROM date) // EXTRACT the specified part of the date FROM date

    MAKEDATE (year, day) // specifies the day of the year and year to generate a date string.

    MAKETIME (hour, minute, second) // Generate a time string

    MONTHNAME (date) // name of the English month

    NOW () // current time

    SEC_TO_TIME (seconds) // converts seconds to time

    STR_TO_DATE (string, format) // Convert string to time, which is displayed in format

    TIMEDIFF (datetime1, datetime2) // Two time difference

    TIME_TO_SEC (time) // time to seconds]

    WEEK (date_time [, start_of_week]) // WEEK

    YEAR (datetime) // YEAR

    DAYOFMONTH (datetime) // The Day of the month

    HOUR (datetime) // HOUR

    LAST_DAY (date) // The last date of the month of date


    MONTH (datetime) // MONTH

    MINUTE (datetime) // return the MINUTE sign, positive or negative or 0

    SQRT (number2) // Square

    I am the dividing line of tiantiao



    Reprinted please indicate the source:

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.