How to change the data type of a field in a table in mysql?
青灯夜游
Release： 2020-10-23 10:20:37
Original
29692 people have browsed it
In mysql, you can use the "ALTER TABLE" statement with the "MODIFY" keyword to change the data type of a field in the table; the syntax format is "ALTER TABLE
ALTER TABLE statement can change the structure of the original table, such as adding or deleting columns, changing the original column type, renaming columns or tables, etc.
The syntax format is as follows:
ALTER TABLE <表名> [修改选项]
Copy after login
The syntax format for modifying options is as follows:
##{ ADD COLUMN ##| CHANGE COLUMN
| ALTER COLUMN { SET DEFAULT | DROP DEFAULT }
| MODIFY COLUMN
| DROP COLUMN
##| RENAME TO
| CHARACTER SET
| COLLATE }
##Modify field data type
#Modifying the data type of a field is to convert the data type of the field into another data type. The syntax rules for modifying field data types in MySQL are as follows:
ALTER TABLE <表名> MODIFY <字段名> <数据类型>
Copy after login
Among them:
Table name: refers to the name of the table where the field whose data type is to be modified is located;
Field name: refers to the field that needs to be modified;
Data type: refers to the new data type of the modified field.
Example:
Create a new student data table in the test database. The SQL statements and running results are as follows:
mysql> USE test;
Database changed
mysql> CREATE TABLE student (
-> id INT(4),
-> name VARCHAR(20),
-> sex CHAR(1));
Query OK, 0 rows affected (0.09 sec)
Copy after login
Use DESC View the student table structure, the SQL statement and the running results are as follows:
mysql> DESC student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
Copy after login
Use ALTER TABLE to modify the structure of the student table, and change the data type of the name field from VARCHAR(20) to VARCHAR(30), the SQL statement and The running results are as follows.
mysql> ALTER TABLE student
-> MODIFY name VARCHAR(30);
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC student;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
Copy after login
After the statement is executed, it is found that the data type of the name field in the table student has been modified to VARCHAR(30), and the modification is successful.
The above is the detailed content of How to change the data type of a field in a table in mysql?. For more information, please follow other related articles on the PHP Chinese website!
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
