ALTER QUERY MYSQL

Alter query is user to change the structure of a database table if it has been created and though it has some data .The alter table gives us the facility to delete,add,rename columns and create/destroy indexes as well as change the data type of already existing table.

Adding Column with ALTER TABLE STATEMENT

The syntax to alter table is :ALTER TABLE tablename ADD COLUMN colname coltype;

The result of the query add column to the end of the table means as a last column its default case. If you want to add the column as first column use “FIRST ” while using add new column to the begining of the table. If you wish to add the new column to next to particular column use “AFTER colname”. See the example below.

ALTER TABLE employee ADD COLUMN Address varchar (200);(in default case)
ALTER TABLE employee ADD COLUMN Adress varchar(200) FIRST;(add as first column)
ALTER TABLE employee ADD COLUMN Adress varchar(200) AFTER age;(add after particular column)

ADD UNIQUE USE:-

If you want a column to be added only if there is no other column already exist in the table with same name use “ADD UNIQUE” in place of ADD COLUMN so that no dublicate column name is added.

Deleting Column using ALTER TABLE STATEMENT

The syntex of deleting existing column is :ALTER TABLE table name DROP COLUMN colname.

If there is data in that particular Drop column that will also be deleted as well.

See the example:-
ALTER TABLE employee DROP COLUMN Address.

Renaming the table with ALTER TABLE STATEMENT

To rename the whole table we use ALTER TABLE . The syntex is :
ALTER TABLE employee RENAME to newtable name;

CHANGE TABLE NAME wth ALTER TABLE STATEMENT

IF you want to change the name of a column the syntex is : ALTER TABLE tablename CHANGE oldcolname newcolname coltype;

ALTER TABLE employee CHANGE Adress permanentadress text;

Changing/modifying the data type of cloumn data with ALTER TABLE STATEMENT

To change /modify the type of a column,the syntax is: ALTER TABLE tablename MODIFY colname coltype;

Mysql ALTER VIEW statement

The ALTER VIEW statement is the same as the CREATE statement, except for the omission of the OR REPLACE option.

The ALTER VIEW statement does the same thing as CREATE OR REPLACE.

The full ALTER statement looks like this:

ALTER [<algorithm attributes>] VIEW [<database>.]< name> [(<columns>)] AS
<SELECT statement> [<check options>]

mysql>
mysql> CREATE TABLE Employee(
    ->     id            int,
    ->     first_name    VARCHAR(15),
    ->     last_name     VARCHAR(15),
    ->     start_date    DATE,
    ->     end_date      DATE,
    ->     salary        FLOAT(8,2),
    ->     city          VARCHAR(10),
    ->     description   VARCHAR(15)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->              values (1,'Jason',    'Martin',  '19960725',  '20060725', 1234.56, 'Toronto',  'Programmer');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(2,'Alison',   'Mathews',  '19760321', '19860221', 6661.78, 'Vancouver','Tester');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(3,'James',    'Smith',    '19781212', '19900315', 6544.78, 'Vancouver','Tester');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(4,'Celia',    'Rice',     '19821024', '19990421', 2344.78, 'Vancouver','Manager');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(5,'Robert',   'Black',    '19840115', '19980808', 2334.78, 'Vancouver','Tester');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(6,'Linda',    'Green',    '19870730', '19960104', 4322.78,'New York',  'Tester');
Query OK, 1 row affected (0.02 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(7,'David',    'Larry',    '19901231', '19980212', 7897.78,'New York',  'Manager');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(8,'James',    'Cat',     '19960917',  '20020415', 1232.78,'Vancouver', 'Tester');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id   | first_name | last_name | start_date | end_date   | salary  | city      | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
|    1 | Jason      | Martin    | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto   | Programmer  |
|    2 | Alison     | Mathews   | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester      |
|    3 | James      | Smith     | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester      |
|    4 | Celia      | Rice      | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager     |
|    5 | Robert     | Black     | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester      |
|    6 | Linda      | Green     | 1987-07-30 | 1996-01-04 | 4322.78 | New York  | Tester      |
|    7 | David      | Larry     | 1990-12-31 | 1998-02-12 | 7897.78 | New York  | Manager     |
|    8 | James      | Cat       | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester      |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)

mysql>
mysql>
mysql>
mysql> Create VIEW myView (id,first_name,city)
    -> AS SELECT id, first_name, city FROM employee;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> select * from myView;
+------+------------+-----------+
| id   | first_name | city      |
+------+------------+-----------+
|    1 | Jason      | Toronto   |
|    2 | Alison     | Vancouver |
|    3 | James      | Vancouver |
|    4 | Celia      | Vancouver |
|    5 | Robert     | Vancouver |
|    6 | Linda      | New York  |
|    7 | David      | New York  |
|    8 | James      | Vancouver |
+------+------------+-----------+
8 rows in set (0.00 sec)

mysql>
mysql>
mysql> ALTER VIEW myView (id,first_name,city)
    -> AS SELECT id, upper(first_name), city FROM employee;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> select * from myView;
+------+------------+-----------+
| id   | first_name | city      |
+------+------------+-----------+
|    1 | JASON      | Toronto   |
|    2 | ALISON     | Vancouver |
|    3 | JAMES      | Vancouver |
|    4 | CELIA      | Vancouver |
|    5 | ROBERT     | Vancouver |
|    6 | LINDA      | New York  |
|    7 | DAVID      | New York  |
|    8 | JAMES      | Vancouver |
+------+------------+-----------+
8 rows in set (0.02 sec)

mysql>
mysql> drop view myView;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql>
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)

mysql>

MySQL utf8 vs utf8mb4 – What’s the difference between utf8 and utf8mb4?

The Story of UTF8 VS UTF8MB4

I once got a call from the support team, saying that one of our customers reported that the application fails to save data in one of our business-critical features. The customer is seeing a general error from the application. About 30 of his 500 users are experiencing this issue and can't save data in the application.

After a short 15 minutes debugging session, we saw that the data is transmitted from the client side, successful received in the server side, and the insertion query is fired to the database. But still, no data in the database. Hmm.. now it got interesting.

Looking at the logs, it turns out that for specific inputs, MySQL refused to insert the data to the database. The error MySQL logged was:

Incorrect string value: '\xF0\x9F\x98\x81...' for column 'data' at row 1

Looking at those first 4 bytes, I got to no conclusion as to what was the issue. When googling them, I found that they represent an emoticon in UTF8. So, it means that for this specific input, each character is probably encoded as 4 bytes.

I tried to reproduce this issue with a different string, which has its characters encoded with 1-3 bytes per character. It turned out that it only happens when each character in the data was combined of 4-byte.

We scratched our heads and thought - the character set we used in the database is UTF8, which should support 4 bytes (right?), so what's wrong?

Well, it turns out we were wrong. We quickly realized that MySQL decided that UTF-8 can only hold 3 bytes per character. Why? no good reason that I can find documented anywhere. Few years later, when MySQL 5.5.3 was released, they introduced a new encoding called utf8mb4, which is actually the real 4-byte utf8 encoding that you know and love.

Recommendation

if you're using MySQL (or MariaDB or Percona Server), make sure you know your encodings. I would recommend anyone to set the MySQL encoding to utf8mb4. Never use utf8 in MySQL, there is no good reason to do that (unless you like tracing encoding related bugs).

How to convert utf8 to utf8mb4 in MySQL?

So now I had to fix this issue. As I recommend above, I wanted to use utf8mb4 and drop the old utf8. To do that, I used the following ALTER statements. Please DO NOT just copy paste them. You need to make sure you understand each of them and adjust them accordingly.

1 2 3 4 5 6 7 8

# Run this once on each schema you have (Replace database_name with your schema name) ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;   # Run this once for each table you have (replace table_name with the table name) ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;   # Run this for each column (replace table name, column_name, the column type, maximum length, etc.) ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Please note that you'll have to consider the consequences of increasing the column size from 3 bytes per character to 4. For example, MySQL indexes are limited to 768 bytes. This means that if you increase VARCHAR(255) from 3 bytes per character to 4 bytes per character, you won't meet that limit anymore.

To conclude, make sure you read about the internals of every decision you make with MySQL. Oh, and use utf8mb4 instead of utf8 without even thinking about it.