Found 4219 Articles for MySQLi

As we know that with the help of QUOTE() function we can put the values of a column in single quotes. By using QUOTE() function with UPDATE clause we can update the table having quoted values. We need to give column name as the parameter of QUOTE() function. Following example will update the table ‘examination_btech’ after putting the values of column ‘Course’ in single quotes. Example mysql> UPDATE examination_btech SET Course = QUOTE(Course); Query OK, 10 rows affected (0.05 sec) mysql> Select * from examination_btech; +--------+----------+----------+ | RollNo | Name | Course ... Read More

We can update MySQL table after padding a string with the values of a column by using LPAD() or RPAD() function along with UPDATE clause. Following the example from ‘examination_btech’ table will make it clearer −ExampleSuppose if we want to append the values, in last, of column course with the string ‘(CSE)’ and want to update the table too then it can be done with the help of the following query −mysql> Update examination_btech set course = RPAD(Course, 11, '(CSE)'); Query OK, 10 rows affected (0.16 sec) mysql> Select * from examination_btech; +-----------+----------+-------------+ | RollNo    | Name   ... Read More

Just like LENGTH() function, MySQL BIT_LENGTH() function is not a multi-byte safe function. As we know that the difference of the result between multi-byte safe functions, like CHAR_LENGTH() or CHARACTER_LENGTH(), and BIT_LENGTH() function especially relevant for Unicode, in which most of the characters are encoded in two bytes or relevant for UTF-8 where the number of bytes varies. It is demonstrated in the example below −Examplemysql> Select BIT_LENGTH('tutorialspoint'); +------------------------------+ | BIT_LENGTH('tutorialspoint') | +------------------------------+ | 112                          | +------------------------------+ 1 row in set (0.00 sec)The above result set shows that ... Read More

We can see only the list of stored functions in a particular MySQL database by the following query −mysql> SELECT ROUTINE_TYPE, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'query' AND ROUTINE_TYPE = 'FUNCTION'// +--------------+--------------------+ | ROUTINE_TYPE | ROUTINE_NAME       | +--------------+--------------------+ | FUNCTION     | factorial          | | FUNCTION     | Hello              | +--------------+--------------------+ 2 rows in set (0.07 sec)

If we want to use prepared statements in a stored procedure then it must be written inside the BEGIN and END block. To understand it, we are creating an example with the help of which we can get all the records from a table by passing the name of the table as a parameter of the stored procedure.Examplemysql> DELIMITER // mysql> Create procedure tbl_detail(tab_name Varchar(40))     -> BEGIN     -> SET @A:= CONCAT('Select * from', ' ', tab_name);     -> Prepare stmt FROM @A;     -> EXECUTE stmt;     -> END // Query OK, 0 ... Read More

The most significant difference between procedures and functions is that they are invoked differently and for different purposes. Other than that following are the differences between procedure and functions −A procedure does not return a value. Instead, it is invoked with a CALL statement to perform an operation such as modifying a table or processing retrieved records.On the other hand, a function is invoked within an expression and returns a single value directly to the caller to be used in the expression. That is, a function is used in expressions the same way as a constant, a built-in function, or ... Read More

mysql> DELIMITER // mysql> CREATE PROCEDURE get_factorial(IN N INT)     -> BEGIN     ->    SET @@GLOBAL.max_sp_recursion_depth = 255;     ->    SET @@session.max_sp_recursion_depth = 255;     ->     ->    CALL factorial_recursive (N, @factorial);     ->     ->    SELECT @factorial;     -> END // Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER // mysql> CREATE PROCEDURE factorial_recursive(IN N INT, OUT factorial INT)     -> BEGIN     ->    IF N = 1 THEN     ->       SET factorial := 1;     -> ... Read More

With the help of MySQL MAKE_SET() function, we can return the values of columns from MySQL table as a set of values. To understand it, we are taking the example of Student_Name table which has the following data −mysql> Select * from Student_Name; +---------+-------+---------+ | FName   | Mname | Lname   | +---------+-------+---------+ | Rahul   | NULL  | Singh   | | Gaurav  | Kumar | NULL    | | Harshit | NULL  | Khurana | | Yash    | Pal   | Sharma  | +---------+-------+---------+ 4 rows in set (0.00 sec)Now, suppose if we want to make ... Read More

MySQL MAKE_SET() function will return nothing if there are all NULL at the place of strings. Following example will demonstrate it −Examplemysql> Select MAKE_SET(2, NULL,NULL,NULL); +-----------------------------+ | MAKE_SET(2, NULL,NULL,NULL) | +-----------------------------+ |                             | +-----------------------------+ 1 row in set (0.00 sec)

A stored procedure is called recursive if it calls itself. Basically, this concept is called recursion. MySQL limits the recursion so the errors will be less rigorous. We can check this limit with the help of the following query −mysql> Show variables LIKE '%recur%'; +------------------------+-------+ | Variable_name          | Value | +------------------------+-------+ | max_sp_recursion_depth |   0   | +------------------------+-------+ 1 row in set (0.01 sec)We can change this value up to 255 with the help of the following query −mysql> SET @@GLOBAL.max_sp_recursion_depth = 255// Query OK, 0 rows affected (0.00 sec) mysql> Show variables LIKE ... Read More