Found 4219 Articles for MySQLi

You need to use NULLIF() function from MySQL. The syntax is as follows:SELECT NULLIF(yourCoumnName, ’ ’) as anyVariableName from yourTableName;In the above syntax, if you compare empty string( ‘ ‘) to empty string( ‘ ‘), the result will always be NULL. However, if you compare with NULL to empty string( ‘ ‘) then also the result will always be NULL.To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table ConvertEmptyStringToNULL    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(20),    -> PRIMARY KEY(Id)   ... Read More

To select the top 2 rows from each group, use the where condition with subquery. Let us create a table. The query to create a table is as follows:mysql> create table selectTop2FromEachGroup    -> (    -> Name varchar(20),    -> TotalScores int    -> ); Query OK, 0 rows affected (0.80 sec)Now insert some records in the table using insert command. The query is as follows:mysql> insert into selectTop2FromEachGroup values('John', 32); Query OK, 1 row affected (0.38 sec) mysql> insert into selectTop2FromEachGroup values('John', 33); Query OK, 1 row affected (0.21 sec) mysql> insert into selectTop2FromEachGroup values('John', 34); Query OK, ... Read More

To add a new column that counts the number of rows as serial number, you can use the global variable in select statement.Let us create a table. The query to create a table is as follows:mysql> create table addColumnToCountAsSerialNumber    -> (    -> Id int,    -> Name varchar(20),    -> Age int,    -> Salary int    -> ); Query OK, 0 rows affected (0.80 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into addColumnToCountAsSerialNumber values(10, 'John', 23, 8576); Query OK, 1 row affected (0.10 sec) mysql> insert into addColumnToCountAsSerialNumber values(12, ... Read More

The equivalent of SQL Server function SCOPE_IDENTITY() is equal to LAST_INSERT_ID() in MySQL. The syntax is as follows:SELECT LAST_INSERT_ID().This returns the id of last inserted record.Here, I am going to create a table with primary key column. The following is the demo of last_insert_id().First, let us create two tables. The query to create the first table table is as follows:mysql> create table TestOnLastInsertIdDemo    -> (    -> StudentId int NOT NULL AUTO_INCREMENT,    -> PRIMARY KEY(StudentId)    -> ); Query OK, 0 rows affected (0.95 sec)Now creating the second table. The query is as follows:mysql> create table TestOnLastInsertIdDemo2   ... Read More

To match only one character in MySQL, you can use underscore(_) in place of %. The syntax is as follows:SELECT *FROM yourTableName WHERE yourColumnName LIKE ‘yourString_’;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table OneCharactermatchDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> PassoutYear year,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.76 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into OneCharactermatchDemo(PassoutYear) values('2008'); Query OK, 1 row affected (0.14 sec) mysql> ... Read More

You need to use CAST() or CONVERT() function to convert output of MySQL query to UTF8. Here, I am using MySQL version 8.0.12. Let us first check the version:mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.12 | +-----------+ 1 row in set (0.00 sec)In this if you use utf8 then you will get warning of aliases because it has utf8mb4. Therefore, you can avoid the warning by placing utf8mb4.Note: Never use UTF8. For current versions, use UTF8MB4Here is the syntax to convert output of MySQL query to UTF8:SELECT yourColumnName1, convert(yourColumnName2 USING utf8) as anyVariableName FROM ... Read More

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