MySQL Articles - Page 348 of 439

It can be illustrated with the help of an example in which we are creating a stored generated column in the table named ‘employee_data_stored’. As we know that stored generated column can be generated by using the keyword ‘stored’.Examplemysql> Create table employee_data_stored(ID INT AUTO_INCREMENT PRIMARY KEY, First_name VARCHAR(50) NOT NULL, Last_name VARCHAR(50) NOT NULL, FULL_NAME VARCHAR(90) GENERATED ALWAYS AS(CONCAT(First_name, ' ', Last_name)) STORED); Query OK, 0 rows affected (0.52 sec) mysql> DESCRIBE employee_data_stored; +------------+-------------+------+-----+---------+------------------+ | Field      | Type        | Null | Key | Default | Extra            | +------------+-------------+------+-----+---------+------------------+ | ... Read More

For adding MySQL virtual GENERATED COLUMNS in a table, we can use the same syntax as adding a column just adding “AS(expression)” after the data type. Its syntax would be as follows −SyntaxALTER TABLE table_name ADD COLUMN column_name AS(expression);Examplemysql> ALTER TABLE employee_data ADD COLUMN FULLName Varchar(200) AS(CONCAT_WS(" ", 'First_name', 'Last_name')); Query OK, 0 rows affected (0.49 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> Describe employee_data; +------------+--------------+------+-----+---------+-------------------+ | Field      | Type         | Null | Key | Default | Extra             | +------------+--------------+------+-----+---------+-------------------+ | ID         ... Read More

It can be illustrated with the help of an example in which we are creating a stored generated column in the table named ‘triangle_stored’. As we know that stored generated column can be generated by using the keyword ‘stored’.Examplemysql> Create table triangle_stored(SideA DOUBLE, SideB DOUBLE, SideC DOUBLE AS (SQRT(SideA * SideB + SideB * SideB)) STORED); Query OK, 0 rows affected (0.47 sec) mysql> Describe triangle_stored; +-------+--------+------+-----+---------+------------------+ | Field | Type   | Null | Key | Default | Extra            | +-------+--------+------+-----+---------+------------------+ | SideA | double | YES  |     | NULL   ... Read More

It can be illustrated with the help of an example in which we are creating a virtual generated column in the table named ‘triangle’. As we know that virtual generated column can be generated with or without using the keyword ‘virtual’.Examplemysql> Create table triangle(SideA DOUBLE, SideB DOUBLE, SideC DOUBLE AS (SQRT(SideA * SideB + SideB * SideB))); Query OK, 0 rows affected (0.44 sec) mysql> Describe Triangle; +-------+--------+------+-----+---------+-------------------+ | Field | Type   | Null | Key | Default | Extra             | +-------+--------+------+-----+---------+-------------------+ | SideA | double | YES  |     | ... Read More

It can be illustrated with the help of an example in which we are creating a virtual generated column in the table named ‘employee_data’. As we know that virtual generated column can be generated with or without using the keyword ‘virtual’.Examplemysql> Create table employee_data(ID INT AUTO_INCREMENT PRIMARY KEY,        First_name VARCHAR(50) NOT NULL, Last_name VARCHAR(50) NOT NULL,        FULL_NAME VARCHAR(90) GENERATED ALWAYS AS(CONCAT(First_name, '', Last_name))); Query OK, 0 rows affected (0.55 sec) mysql> DESCRIBE employee_data; +------------+-------------+------+-----+---------+-------------------+ | Field      | Type        | Null | Key | Default | Extra     ... Read More

We have two types of MYSQL generated columns as follows −VIRTUAL GENERATED COLUMNAs the name suggests, this kind of generated column will not take any disk space. It can be generated with or without using the keyword ‘virtual’. To understand we are illustrating it in the following example −Examplemysql> Create table triangle(SideA DOUBLE, SideB DOUBLE, SideC DOUBLE AS (SQRT(SideA * SideB + SideB * SideB))); Query OK, 0 rows affected (0.44 sec) mysql> Describe Triangle; +-------+--------+------+-----+---------+-------------------+ | Field | Type   | Null | Key | Default | Extra             | +-------+--------+------+-----+---------+-------------------+ | SideA ... Read More

Basically generated columns are a feature that can be used in CREATE TABLE or ALTER TABLE statements and is a way of storing the data without actually sending it through the INSERT or UPDATE clause in SQL. This feature has been added in MySQL 5.7. A generated column works within the table domain. Its syntax would be as follows −Syntaxcolumn_name data_type [GENERATED ALWAYS] AS (expression) [VIRTUAL | STORED] [UNIQUE [KEY]]Here, first of all, specify the column name and its data type.Then add the GENERATED ALWAYS clause to indicate that the column is a generated column.Then, indicate whether the type of ... Read More

We can write the following PHP script to get the list of available MySQL databases −Example

PHP uses mysql_affected_rows( ) function to find out how many rows a query changed. To illustrate it we are having the following example −Example           Rows affected by query                  

PHP uses mysql_affected_rows( ) function to find out how many rows a query changed. This function basically returns the number of affected rows in the previous SELECT, INSERT, UPDATE, REPLACE, or DELETE query. Return of an integer > 0 indicates the number of rows affected, 0 indicates that no records were affected and -1 indicates that the query returned an error. Its syntax is as follows −Syntaxmysql_affected_rows( connection );Followings are the parameters used in this function −S. No.Parameter & Description1.ConnectionRequired – Specifies the MySQL connection to use