Found 4219 Articles for MySQLi

The millisecond/ microsecond precision wasn’t supported in previous versions like 5.6.4. But now MySQL supports millisecond/ microsecond precision with timestamp, datetime, and time. The official statement. “MySQL now supports fractional seconds for TIME, DATETIME, and TIMESTAMP values, with up to microsecond precision”. You can check the MySQL version on your system using the version() method. mysql> SELECT version(); The following is the output. +-----------+ | version() | +-----------+ | 8.0.12 | +-----------+ 1 row in set (0.01 sec) Let us now see the syntax to check the date difference. mysql> SELECT DATEDIFF(now(), ... Read More

Let us first create a table. The CREATE command is used to create a table. mysql> create table Table1 -> ( -> id int, -> name varchar(100) -> ); Query OK, 0 rows affected (0.62 sec) Let us now create another table. mysql> create table Table2 -> ( -> id int, -> name varchar(100) -> ); Query OK, 0 rows affected (0.49 sec) Now, the following is how you can create a ... Read More

The debug Lock wait timeout situation occurs because of some threads. If one thread is holding on to some records for a very long time, it means the thread has exceeded time. To see all the details, implement the following query − mysql> SHOW ENGINE INNODB STATUS; The following is the output. +--------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Type | Name | Status ... Read More

To convert the MyISAM Engine to InnoDB, we can use the ALTER command. Let us now create a table with the help of engine MyISAM. mysql> create table MyISAMToInnoDBDemo -> ( -> id int, -> Name varchar(100) -> )ENGINE=MyISAM; Query OK, 0 rows affected (0.19 sec) To check if the table is created with engine MyISAM or not. mysql> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'business' and ENGINE = 'MyISAM'; The following is the output that displays the table created with MyISAM ... Read More

To create a MySQL table with MyISAM engine, we can use ENGINE command. Let us first create a table using CREATE command. mysql> create table StudentRecordWithMyISAM -> ( -> Id int, -> StudentName varchar(100), -> StudentAge int -> )ENGINE=MyISAM; Query OK, 0 rows affected (0.26 sec) Above, we have set the ENGINE as “MyISAM”. To check how many columns are present in the table, use DESC command. mysql> DESC StudentRecordWithMyISAM; The following is the output. +-------------+--------------+------+-----+---------+-------+ | Field ... Read More

To create a table with InnoDB engine, we can use the ENGINE command. Here is the query to create a table.mysql> create table EmployeeRecords - > ( - > EmpId int, - > EmpName varchar(100), - > EmpAge int, - > EmpSalary float - > )ENGINE=INNODB; Query OK, 0 rows affected (0.46 sec)We have set the ENGINE as INNODB above.Check the full description about the table using the DESC command.mysql> DESC EmployeeRecords;The following is the output.+-----------+--------------+------+-----+---------+-------+ | Field     | Type         | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+-------+ | EmpId     ... Read More

We can enable the MySQL slow query log with the help of SET statement.The following is the syntax.SET GLOBAL slow_query_log = 'Value';In the above syntax, value can be filled with ON/OFF. To enable slow query log, let us see the query.mysql> SET GLOBAL slow_query_log = 'ON'; Query OK, 0 rows affected (0.00 sec)To check if the slow query is ON, implement the following query −mysql> SHOW GLOBAL VARIABLES LIKE 'slow\_%'; Here is the output.+---------------------+--------------------------+ | Variable_name       | Value                    | +---------------------+--------------------------+ | slow_launch_time    | 2       ... Read More

Function-based index wasn’t possible in MySQL versions below 5.6. Firstly, to create function-based index in MySQL, we will create a table. mysql> create table FunctionIndexDemo - > ( - > FirstName varchar(100) - > ); Query OK, 0 rows affected (0.70 sec) Let us see the syntax to create a function based index. create index index_name on yourTableName (column_name(IntegerSize)); Here is the query. mysql> create index indFirstName on FunctionIndexDemo (FirstName(6)); Query OK, 0 rows affected (0.56 sec) Records: 0 Duplicates: 0 Warnings: 0 ... Read More

We can escape apostrophe (‘) in MySQL in the following two ways − We can use backslash. We can use single quotes twice (double quoted) Using backslash Let us first create a table. mysql> create table SingleQuotesDemo - > ( - > id int, - > name varchar(100) - > ); Query OK, 0 rows affected (1.16 sec) Following direct usage does not give the desired result for name “John’s”. mysql> insert into SingleQuotesDemo values(1, 'John's'); '> Let us now use backslash. ... Read More

InnoDB is a general-purpose storage engine that balances high reliability and performance. Since MySQL 5.6, InnoDB is the default MySQL storage engine. InnoDB can be used for various purposes. Here are some of them − It can be used for transaction purpose i.e. all ACID properties. InnoDB can be used for row level locking, that means it gives higher performance as compared to MyISAM. InnoDB can be used for both data and index for a large buffer pool. InnoDB can be used when we need better performance than MyISAM. MyISAM is the default storage engine for the MySQL ... Read More