Found 4219 Articles for MySQLi

To get the exact number if table and columns in a MySQL database, use the DISTINCT inside COUNT().Let’s say we have a database ‘sample’ and we need to work on it to get the exact number of table and columns.To achieve it, the query is as follows −mysql> SELECT COUNT(DISTINCT TABLE_NAME) AS TotalTable, Count(Column_Name) AS TOTALColumn -> FROM INFORMATION_SCHEMA.COLUMNS -> WHERE TABLE_SCHEMA = 'sample';The following is the output displaying the count of table and columns in the database ‘sample’ −+------------+-------------+ | TotalTable | TOTALColumn | +------------+-------------+ | ... Read More

Let us first create a stored procedure −mysql> DELIMITER // mysql> CREATE PROCEDURE ExtenddatesWithMonthdemo(IN date1 datetime, IN NumberOfMonth int )    -> BEGIN    -> SELECT DATE_ADD(date1, INTERVAL NumberOfMonth MONTH) AS ExtendDate;    -> END;    -> // Query OK, 0 rows affected (0.20 sec) mysql> DELIMITER ;Now you check whether the stored procedure exists with the help SHOW CREATE command.The query is as follows −mysql> SHOW CREATE PROCEDURE ExtenddatesWithMonthdemo; The following is the output displaying the details of the stored procedure we created above: +--------------------------+--------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | Procedure                | sql_mode ... Read More

You can achieve case sensitive uniqueness and case insensitive search with the help of the following two ways −VARBINARY data type_bin collationVARBINARY data typeTo work with the VARBINARY data type, let us first create a table. The query to create a table is as follows −mysql> create table SearchingDemo2 -> ( -> UserId VARBINARY(128) NOT NULL, -> UNIQUE KEY index_on_UserId2(UserId ) -> )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; Query OK, 0 rows affected, 1 warning (0.99 sec)Keep in mind UserId has data type VARBINARY(128) and Index(‘index_on_UserId2’) on a column ‘UserId’._bin ... Read More

The following is the syntax to check whether a table is empty or not using MySQL EXISTS −SELECT EXISTS(SELECT 1 FROM yourTableName);ExampleFirst, let us create a table. The query to create a table is as follows −mysql> create table ReturnDemo    -> (    -> Id int,    -> Name varchar(10)    -> ); Query OK, 0 rows affected (0.79 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into ReturnDemo values(100, 'Larry'); Query OK, 1 row affected (0.18 sec) mysql> insert into ReturnDemo values(101, 'Bob'); Query OK, 1 row affected (0.28 sec) ... Read More

If you use COUNT(*) around the LEAST() then MySQL scans at least one index, therefore avoid LEAST(COUNT(*)) and use LIMIT.Let us first create a table. The query to create a table is as follows −mysql> create table ReturnDemo -> ( -> Id int, -> Name varchar(10) -> ); Query OK, 0 rows affected (0.79 sec)ExampleNow you can insert some records in the table using insert command. The query is as follows −mysql> insert into ReturnDemo values(100, 'Larry'); Query OK, 1 row affected (0.18 sec) mysql> insert into ReturnDemo values(101, 'Bob'); Query OK, 1 row affected (0.28 sec) mysql> insert into ... Read More

First, you need to open my.cnf file. The following is the query to get the directory location of the config file on Windows −mysql> select @@datadir;Output+---------------------------------------------+ | @@datadir | +---------------------------------------------+ | C:\ProgramData\MySQL\MySQL Server 8.0\Data\ | +---------------------------------------------+ 1 row in set (0.00 sec)Here is the snapshot of the directory −Now open my.cnf file. The snapshot is as follows −If you want to add more data to cache, ... Read More

Error occurs when you set a negative value to UNSIGNED column in MySQL. For example, let us first create a table with an UNSIGNED field −mysql> create table UnsignedDemo    -> (    -> Id int UNSIGNED    -> ); Query OK, 0 rows affected (0.79 sec)The error is as follows whenever you insert negative value to column Id which is declared as UNSIGNED −mysql> INSERT INTO UnsignedDemo VALUES(-100); ERROR 1264 (22003): Out of range value for column 'Id' at row 1ExampleHowever, positive values work well for UNSIGNED. The same is shown in the example below. Insert some records in ... Read More

To get the average string length in MySQL, we will work around a query that gets rows from 1 to 10 and displays the result.Let us first create a table. The query to create a table is as follows −mysql> create table AverageString -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Value varchar(20) -> ); Query OK, 0 rows affected (0.66 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into AverageString(Value) values('MySQL Query'); Query OK, 1 row ... Read More

To understand the GROUP BY and MAX on multiple columns, let us first create a table. The query to create a table is as follows −mysql> create table GroupByMaxDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> CategoryId int,    -> Value1 int,    -> Value2 int    -> ); Query OK, 0 rows affected (0.68 sec)ExampleInsert some records in the table using insert command. The query is as follows −mysql> insert into GroupByMaxDemo(CategoryId, Value1, Value2) values(10, 100, 50); Query OK, 1 row affected (0.15 sec) mysql> insert into GroupByMaxDemo(CategoryId, Value1, Value2) values(10, 100, ... Read More

The SHOW TABLE STATUS in MySQL displays the NAME, ENGINE, VERSION, ROWS, CHECKSUM, etc of a table −ExampleLet us first create a table. Here, we are using the MyISAM engine. The query to create a table is as follows −mysql> create table Post_Demo    -> (    -> PostId int,    -> PostName varchar(100),    -> PostDate datetime,    -> PRIMARY KEY(PostId)    -> )ENGINE = MyISAM; Query OK, 0 rows affected (0.28 sec)Now you can check the table status using SHOW TABLE command. The query is as follows −mysql> show table status where Name = 'Post_Demo'\GOutput*************************** 1. row *************************** ... Read More