Found 4219 Articles for MySQLi

As we know that a query can have multiple MySQL statements followed by a semicolon. Suppose if we want to get the result from multiple tables then consider the following example to get the result set from ‘Student_info’ and ‘Student_detail’ by writing a single query −mysql> Select Name, Address from Student_info; Select Studentid, Address from Student_detail; +---------+------------+ | Name    | Address    | +---------+------------+ | YashPal | Amritsar   | | Gaurav  | Chandigarh | | Raman   | Shimla     | | Ram     | Jhansi     | | Shyam   | Chandigarh | | ... Read More

The representation of an empty string in result set depends on data type when we insert an empty string into a MySQL column which is declared as NOT NULL. As we know that on inserting empty string we are providing value to MySQL that has integer representation as INT 0.Now, if that column is having INTEGER data type then MySQL would show 0 in the result set as that empty string has been mapped to zero as an integer.Examplemysql> create table test(id int NOT NULL, Name Varchar(10)); Query OK, 0 rows affected (0.19 sec) mysql> Insert into test(id, name) ... Read More

By executing SELECT @@TX_ISOLATION command we can check the current MySQL transaction isolation level.Examplemysql> SELECT @@TX_ISOLATION; +-----------------+ | @@TX_ISOLATION  | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec)

It is because inserting an empty string means that we are inserting some value and not NULL. The empty string apparently maps to zero as an integer. In other words, we can say that by inserting empty string we are providing a value to MySQL that has integer representation as INT 0. Consider the following example in which we inserted an empty string and it mapped to 0 by MySQL.mysql> create table test(id int NOT NULL, Name Varchar(10)); Query OK, 0 rows affected (0.19 sec) mysql> Insert into test(id, name) values('1', 'Gaurav'), ('0', 'Rahul'), ('', 'Aarav'); Query OK, 3 ... Read More

Declaring a column ‘NOT NULL’ means that this column would not accept NULL values but zero (0) and an empty string is itself a value. Hence there would be no issue if we want to insert zero or an empty string into a MySQL column which is defined as NOT NULL. Following comparisons of 0 and empty string with NULL would make it clear −mysql> Select 0 IS NULL, 0 IS NOT NULL; +-----------+---------------+ | 0 IS NULL | 0 IS NOT NULL | +-----------+---------------+ |         0 |             1 | ... Read More

The reason behind it is that we will not receive any meaningful results from the comparisons when we use NULL with the comparison operators like ‘=’, ‘ Select 10 = NULL, 10< NULL, 10NULL; +-----------+----------+----------+ | 10 = NULL | 10< NULL | 10NULL | +-----------+----------+----------+ |      NULL |     NULL |     NULL | +-----------+----------+----------+ 1 row in set (0.07 sec)The above result set is not meaningful in any sense.

The current transaction will be committed and ended if START TRANSACTION is executed in the middle of a current transaction. All the database changes made in the current transaction will be made permanent. This is called an implicit commit by a START TRANSACTION command.ExampleSuppose we have the following values in table ‘marks’mysql> select * from marks; +------+---------+-----------+-------+ | Id   | Name    | Subject   | Marks | +------+---------+-----------+-------+ | 1    | Aarav   | Maths     | 50    | | 1    | Harshit | Maths     | 55    | | 3   ... Read More

The current MySQL transaction will be committed and ended when any of the DDL statement such as CREATE or DROP databases, Create, ALTER or DROP tables or stored routines is executed in the middle of the current transaction. All the database changes made in the current transaction will be made permanent and cannot be rolled back.Examplemysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO MARKS Values(6, 'Manak', 'History', 70); Query OK, 1 row affected (0.26 sec) mysql> Create table student(id int, Name Varchar(10), ); Query OK, 0 rows affected (0.84 sec)As we can see ... Read More

Suppose if a session is ended in the middle of a transaction then that current MySQL transaction will be rolled back by MySQL and ended. It means that all the database changes made in the current transaction will be removed. It is called n implicit rollback when the session is ended.ExampleSuppose we have the following values in the table ‘marks’mysql> Select * from marks; +------+---------+-----------+-------+ | Id   | Name    | Subject   | Marks | +------+---------+-----------+-------+ | 1    | Aarav   | Maths     | 50    | | 1    | Harshit | Maths   ... Read More

Suppose if a session is killed in the middle of a transaction then that current MySQL transaction will be rolled back by MySQL and ended. It means that all the database changes made in the current transaction will be removed. It is called n implicit rollback when the session is killed.ExampleSuppose we have the following values in the table ‘marks’mysql> Select * from marks; +------+---------+-----------+-------+ | Id   | Name    | Subject   | Marks | +------+---------+-----------+-------+ |    1 | Aarav   | Maths     |    50 | |    1 | Harshit | Maths   ... Read More