Found 4219 Articles for MySQLi

We can convert the timestamp to date time with the help of FROM_UNIXTIME() function. Let us see an example. First, we will create a table with column of int type. Then we convert it to timestamp and again into date time. Creating a table with integer type. mysql> create table TimestamptoDateDemo -> ( -> YourTimeStamp int(11) -> ); Query OK, 0 rows affected (0.57 sec) Inserting records into the table. mysql> insert into TimestamptoDateDemo values(1389453221); Query OK, 1 row affected (0.23 sec) To display all the ... Read More

BLOB stands for Binary Large Objects and as its name suggests, it can be used for storing binary data while TEXT is used for storing large number of strings. BLOB can be used to store binary data that means we can store pictures, videos, sounds and programs also. For example, the following image can be stored into BLOB because the image has binary data. BLOB values behave like byte string and BLOB does not have a character set. Therefore, comparison and sorting is fully dependent upon numeric values of bytes. TEXT values behave like non-binary string or character string. ... Read More

To find the number of columns in a MySQL table, use the count(*) function with information_schema.columns and the WHERE clause. Let us see an example. Creating a table. mysql> create table NumberOfColumns -> ( -> id int, -> FirstName varchar(100), -> LastName varchar(100), -> Age int, -> Address varchar(100) -> ); Query OK, 0 rows affected (0.70 sec) Inserting records into table. mysql> insert into NumberOfColumns values(1, 'Shane', 'Watson', 36, 'Australia'); Query OK, 1 row affected ... Read More

The ‘L’ suffix concept in MySQL can be related with Python. In Python 2, the long integer literal is suffixed with L or l, but int and long have been binded into int in version 3. Therefore, there is no need for L or l. Adding large numbers in Python Version 3.7 (Python 3), without using any suffix. Here, if we suffix L or l, Python 3 gives an error. However, Python Version 2 suffixed with L or l will not give an error. The following is the output with no error. Hence, Python int is ... Read More

Non-alphanumeric characters are as follows − @, !, #, &, (), ?, / There is no inbuilt function to remove non-alphanumeric characters from a string in MySQL. Therefore, we create a function which removes all non-alphanumeric characters. The function declaration and definition is as follows. mysql> delimiter // mysql> CREATE FUNCTION RemoveNonAlphaNumeric( s CHAR(255) ) RETURNS CHAR(255) DETERMINISTIC -> BEGIN -> DECLARE var1, length SMALLINT DEFAULT 1; -> DECLARE result CHAR(255) DEFAULT ''; -> DECLARE ch CHAR(1); ... Read More

For our example, we will create two tables and apply Natural Left Join to get the rows from a table not present in the second table. Creating the first table. mysql> create table FirstTableDemo -> ( -> id int, -> name varchar(100) -> ); Query OK, 0 rows affected (0.48 sec) Inserting records into first table. mysql> insert into FirstTableDemo values(1, 'Bob'), (2, 'John'), (3, 'Carol'); Query OK, 3 rows affected (0.13 sec) Records: 3 Duplicates: 0 Warnings: 0 To display all ... Read More

MySQL has the AUTO_INCREMENT keyword to perform auto-increment. The starting value for AUTO_INCREMENT is 1, which is the default. It will get increment by 1 for each new record. To get the next auto increment id in MySQL, we can use the function last_insert_id() from MySQL or auto_increment with SELECT. Creating a table, with "d" as auto-increment. mysql> create table NextIdDemo -> ( -> id int auto_increment, -> primary key(id) -> ); Query OK, 0 rows affected (1.31 sec) Inserting records into the table. mysql> insert into NextIdDemo ... Read More

We can achieve possible enum value with the help of ‘enum’. The following is the syntax. yourColumnName ENUM(value1, value2, ........................N) Let us first create a table. mysql> create table EnumDemo -> ( -> Light int, -> IsONOrOff ENUM('ON', 'OFF') -> ); Query OK, 0 rows affected (1.06 sec) In the above table, ENUM is used to get the possible value for “Light”. Inserting records. mysql> insert into EnumDemo values(1, 'ON'), (0, 'OFF'); Query OK, 2 rows affected (0.24 sec) Records: 2 Duplicates: ... Read More

We can quit/ exit from MySQL stored procedure with the help of the LEAVE command. The following is the syntax. Leave yourLabelName; The following is an example. Here, we are creating a new procedure. mysql> delimiter // mysql> CREATE PROCEDURE ExitQuitDemo2(IN Var1 VARCHAR(20)) -> proc_Exit:BEGIN -> IF Var1 IS NULL THEN -> LEAVE proc_Exit; -> END IF; -> END // Query OK, 0 rows affected (0.16 sec) Above, we have set the following LEAVE command to exit from the procedure. If ... Read More

We can restore the MySQL root user full privileges with the help of UPDATE command. Firstly, you need to stop mysqld and restart it with the --skip-grant-tables option. After that, connect to the mysqld server with only mysql (i.e. no -p option, and username may not be required). Issue the below given command in the mysql client to restore the MySQL root user with full privileges. mysql> UPDATE mysql.user SET Grant_priv = 'Y', Super_priv = 'Y' WHERE User = 'root'; Query OK, 0 rows affected (0.04 sec) Rows matched: 1 Changed: 0 Warnings: 0 Above, ... Read More