There is a single way by which you can set a global event_scheduler=ON even if MySQL is restarted. You need to set global system variable ON and need to use this system variable even if MySQL restart.For this, I am using system variable @@event_scheduler using select statement. The query is as follows:mysql> select @@event_scheduler;The following is the output:+-------------------+ | @@event_scheduler | +-------------------+ | ON | +-------------------+ 1 row in set (0.00 sec)Now, restart MySQL. The query is as follows:mysql> restart; Query OK, 0 rows affected ... Read More

You need to use LIKE with OR operator to find all the names that starts with a or b or c. The syntax is as follows:SELECT *FROM yourTableName WHERE yourColumnName like 'A%' or yourColumnName like 'B%' or yourColumnName like 'C%';The above query finds all names that starts only with the letter ‘a’ or ‘b’ or ‘c’. To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table AllNamesStartWithAorBorC    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> EmployeeName varchar(20),    -> PRIMARY KEY(Id)    -> ); Query ... Read More

NOTE: There is only one difference between AND and && is that AND is a standard while && is ownership syntax.Except the above statement, there is no difference between AND and &&. Let us look at all the conditions.The result of AND and && will always be either 1 or 0. As we know the AND and && both are logical operators, if there are more than one operand and any one of them has value 0 then result becomes 0 otherwise 1.Here is the demo of AND and &&.Case 1(a): If both operands are 1. Using AND.The query is ... Read More

NULL and empty string ‘ ’ both aren’t similar in MySQL. To check the field is empty like ‘ ’or is null, you need to use the IS NULL property or something else. You can check all the conditions with CASE statement. The syntax is as follows:SELECT *, CASE WHEN yourColumnName =’ ‘ THEN ‘yourMessage1’ WHEN yourColumnName IS NULL THEN ‘yourMessage2’ ELSE CONCAT(yourColumnName ,' is a Name') END AS anyVariableName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table checkFieldDemo    -> (    -> Id int ... Read More

In order to see the global locks in MySQL (Innodb), use the SHOW command. The below query shows the global locks as well owner of locks and waiters. The following query will also show transaction id and more related to Innodb.The query is as follows:mysql> SHOW ENGINE INNODB STATUS\GThe following is the output:*************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2019-01-23 14:46:58 0x2914 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 23 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 87 srv_active, 0 srv_shutdown, 51953 srv_idle srv_master_thread log flush and writes: 0 ---------- SEMAPHORES ---------- OS WAIT ... Read More

You need to use CASE statement to conditional select between dates to find the minimum and maximum price. Wrap up the CASE statement with aggregate function MIN() and MAX(). The syntax is as follows:SELECT MIN(CASE WHEN CURDATE() BETWEEN yourStartDateColumnName AND yourEndDateColumnName THEN yourLowPriceColumnName ELSE yourHighPriceColumnName END) AS anyVariableName, MAX(CASE WHEN CURDATE() BETWEEN yourStartDateColumnName AND yourEndDateColumnName THEN yourLowPriceColumnName ELSE yourHighPriceColumnName END) AS anyVariableName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table ConditionalSelect    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> StartDate datetime, ... Read More

You need to use VALUES() with comma separation for multiple insert or batch insert at a time. Use the following syntax that does not produce an invalid MySQL query on insert. The syntax is as follows:INSERT INTO yourTableName VALUES(yourValue1), (yourValue1), (yourValue2), (yourValue3), (yourValue4), (yourValue5), .......N;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table addMultipleValues    -> (    -> Counter int NOT NULL    -> ); Query OK, 0 rows affected (0.60 sec)Now you can insert batch records in the table using VALUES() with comma separation. The query ... Read More

We are using MySQL version 8.0.12. Let us first check the MySQL version:mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.12    | +-----------+ 1 row in set (0.00 sec)To deal with Boolean in MySQL, you can use BOOL or BOOLEAN or TINYINT(1). If you use BOOL or BOOLEAN, then MySQL internally converts it into TINYINT(1).In BOOL or BOOLEAN data type, if you use true literal then MySQL represents it as 1 and false literal as 0 like in PHP/ C/ C++ language.To proof that MySQL convert the BOOL or BOOLEAN to TINYINT(1), let us create a table with ... Read More

The local variable has the scope for only a set of statements or block of statement. Whenever a set of statements or block of statement has completed then local variable goes out of scope.For ExampleLocal variable can be used in stored procedure, function etc. It is used with the DECLARE keyword.The syntax is as follows to local variables.DECLARE yourVariableName dataType;The global variables or system variables has the scope across connections until server restart. It is set using GLOBAL keyword. The syntax is as follows −SET GLOBAL max_connections=value; OR SET @@global.max_connections=value;If you know port number then you can use system variable ... Read More

System VariablesThe system variables are tightly typed variable. These are also known as global specific variable.The system variable can be initialized somewhere in global and contains the value of system variables until the server restarts. This value will destroy whenever you restart the MySQL server. The predefined system variable is prefixed with symbol @@.User-defined VariableThe user defined variable is also known as session-specific variable. It is a type of loosely typed variable which can be initialized somewhere in session and contains the value of user defined variable until session ends. The user defined variable is prefixed with symbol @.For Example:@anyVariableNameRead More