MySQLi Articles - Page 338 of 341

AUTO_INCREMENT means that the column will get the value automatically. To illustrate it we have created a table ‘employees’ as follows − mysql> Show Create Table employees\G *************************** 1. row *************************** Table: employees Create Table: CREATE TABLE `employees` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(35) DEFAULT NULL, PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) From the above result set, we can see that column id is given the auto-increment option. Now, when we will insert the value in Name ... Read More

Suppose we have a table ‘Order123’ having ProductName, Quantity and OrderDate columns as follows −mysql> Select * from Order123; +-------------+----------+------------+ | ProductName | Quantity | OrderDate  | +-------------+----------+------------+ | A           | 100      | 2017-05-25 | | B           | 105      | 2017-05-25 | | C           | 55       | 2017-05-25 | | D           | 250      | 2017-05-26 | | E           | 500      | 2017-05-26 | | ... Read More

The default format for MySQL DATE data type is “YYYY-MM-DD” and in this format, there is no possibility to store the time value. Hence, we can say that we cannot use DATE data type along with time value.As we can see in the following example MySQL returns only date value even on using time along with the date.mysql> select DATE("2017-09-25 09:34:21"); +-----------------------------------+ | DATE("2017-09-25 09:34:21")       | +-----------------------------------+ | 2017-09-25                        | +-----------------------------------+ 1 row in set (0.04 sec)However, in DATETIME and TIMESTAMP date data types we can use the time to date.

MySQL supports following 5 types of DATE data type −DATE - A date is in the range between 1000-01-01 and 9999-12-31. “YYYY-MM-DD” is the default DATE format. For example, January 17th, 1984 would be stored as 1984-01-17.DATETIME − This data type supports a date along with time in the range between 1000-01-01 00:00:00 and 9999-12-31 23:59:59. “YYYY-MM-DD HH:MM:SS” is the default DATETIME format. For example, 2:20 in the afternoon on January 17th, 1984 would be stored as 1984-01-17 14:20:00.TIMESTAMP − A timestamp data type supports a date along with time in the range between ‘1970-01-01 00:00:01’ to ‘2038-01-19 08:44:07’. It ... Read More

MySQL SHOW CREATE TABLE statement will provide us the constraints applied to a particular table along with some other details about that table. Its syntax would be as follows −SyntaxSHOW CREATE TABLE table_name;Here table_name is the name of the table on which we want to see the constraints.ExampleIn this example we are getting the detail of the table named ‘employees’ −mysql> Show Create table employees\G *************************** 1. row ***************************        Table: employees Create Table: CREATE TABLE `employees` (    `Id` int(11) NOT NULL AUTO_INCREMENT,    `Name` varchar(35) DEFAULT NULL,    PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 ... Read More

As we know that in MySQL, VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. This length prefix points out the number of bytes in the value of data. The data value itself will decide that when VARCHAR data type will use 1-byte and when 2-byte prefix length.A column uses 1-byte length if values require no more than 255 bytes.A column uses 2-byte length if values may require more than 255 bytes.

CHAR and VARCHAR are both ASCII character data types and almost same but they are different at the stage of storing and retrieving the data from the database. Following are some important differences between CHAR and VARCHAR in MySQL −CHAR Data TypeVARCHAR Data TypeIts full name is CHARACTERIts full name is VARIABLE CHARACTERIt stores values in fixed lengths and are padded with space characters to match the specified lengthVARCHAR stores values in variable length along with 1-byte or 2-byte length prefix and are not padded with any charactersIt can hold a maximum of 255 characters.It can hold a maximum of 65, ... Read More

In MySQL, both CHAR and NCHAR are ASCII character data types used for storing text data, but they differ significantly in terms of storage, data representation, and performance. CHAR and NCHAR columns can have different collations, determining how strings are compared and sorted. The CHAR type typically uses the collation associated with its specified character set. On the other hand, NCHAR is intended for Unicode data and typically uses a collation that can handle Unicode characters, ensuring proper sorting and comparison. Understanding 'CHAR' in MySQL The CHAR data type is primarily used to store ASCII character data. It is a ... Read More

When two tables are connected with Foreign key and data in the parent table is deleted, for which record exists in child table too, then followings are the ways to maintain data integrity −On Delete CascadeThis option will remove the record from child table too if that value of the foreign key is deleted from the main table.On Delete Null This option will set all the values in that record of child table as NULL, for which the value of the foreign key is deleted from the main table.

We can disable foreign key checks with the help of the following statement −mysql> Set foreign_key_checks = 0; Query OK, 0 rows affected (0.00 sec)And we can enable it with the help of the following statement −mysql> Set foreign_key_checks = 1; Query OK, 0 rows affected (0.00 sec)Some benefits of disabling foreign key checks are as follows −After disabling foreign key checks we can load data into parent and child table in any order. Otherwise, we must have to load the data first in the parent table and then in the child table.Without disabling foreign key checks we cannot drop ... Read More