Shulou(Shulou.com)06/01 Report--

Editor to share with you how to modify the mysql column, I hope you will learn a lot after reading this article, let's discuss it together!

Mysql modify the column method: 1, use "ALTER TABLE table name MODIFY field name field type [integrity constraints]" to modify the column data type; 2, use "ALTER TABLE table name CHANGE old column name new column name new data type;" to modify the column name.

Modify field properties-data type:

-- modify field properties-- ALTER TABLE tb_name MODIFY field name field type [integrity constraints]-- modify email field VARCHAR (50) to VARCHAR (200)-- Note: if you modify without integrity constraints, the original constraints will be lost. If you want to retain the modifications, you have to bring the integrity constraints ALTER TABLE user10 MODIFY email VARCHAR (200) NOT NULL DEFAULT 'aintegra.com'. -- move card behind test ALTER TABLE user10 MODIFY card CHAR (10) AFTER test;-- put test first, retaining the original integrity constraint ALTER TABLE user10 MODIFY test CHAR (32) NOT NULL DEFAULT '123' FIRST

Modify the field name and properties:

Change test field to test1-- ALTER TABLE table name CHANGE original field name new field name field type constraint ALTER TABLE user10 CHANGE test test1 CHAR (32) NOT NULL DEFAULT '123'

Add and delete default values:

-- create a new table CREATE TABLE user11 (id TINYINT UNSIGNED KEY AUTO_INCREMENT,username VARCHAR (20) NOT NULL UNIQUE,age TINYINT UNSIGNED);-- add the default value ALTER TABLE user11 ALTER age SET DEFAUTL 18 to age-add a field ALTER TABLE user11 ADD email VARCHAR (50);-- add the default value to email ALTER TABLE user11 ALTER email SET DEFAULT'a default value, ALTER TABLE user11 ALTER email SET DEFAULT'a default value, and delete the default value ALTER TABLE user11 ALTER age DROP DEFAULT;ALTER TABLE user11 ALTER email DROP DEFAULT

Add a primary key:

-- create a table CREATE TABLE test12 (id INT);-- add a primary key-- ALTER TABLE tb_name ADD [CONSTRAINT [sysmbol]] PRIMARY KEY [index_type] (field name,...) ALTER TABLE test12 ADD PRIMARY KEY (id);-- add a compound primary key-- first create a table CREATE TABLE test13 (id INT,card CHAR (18), username VARCHAR (20) NOT NULL);-- add a compound primary key ALTER TABLE test13 ADD PRIMARY KEY (id,card)

Delete the primary key:

-- delete the primary key ALTER TABLE test12 DROP PRIMARY KEY;-- and add the primary key to test12, the full form of ALTER TABLE test12 ADD CONSTRAINT symbol PRIMARY KEY index_type (id)

When deleting a primary key, it is important to note that we know that a field with a self-growing property must be a primary key, and if the primary key in the table has a self-growing attribute, then a direct deletion will report an error. If you want to delete the primary key, you can first increase the attribute last year, and then delete the primary key.

-- create another table, CREATE TABLE test14 (id INT UNSIGNED KEY AUTO_INCREMENT);-- delete the primary key, which will cause an error, because the self-growing primary key ALTER TABLE test14 DROP PRIMARY KEY;-- must first delete the self-growing attribute with MODIFY. Note that MODIFY cannot remove the primary key attribute ALTER TABLE test14 MODIFY id INT UNSIGNED;-- and then delete the primary key ALTER TABLE test14 DROP PRIMARY KEY.

Unique index:

-- add uniqueness constraint-- ALTER TABLE tb_name ADD [CONSTANT [symbol]] UNIQUE [INDEX | KEY] [Index name] (field name,...)-- create a test table CREATE TABLE user12 (id TINYINT UNSIGNED KEY AUTO_INCREMENT,username VARCHAR (20) NOT NULL,card CHAR (18) NOT NULL,test VARCHAR (20) NOT NULL,test1 CHAR (32) NOT NULL) -- username adds uniqueness constraint. If no index name is specified, the system will index ALTER TABLE user12 ADD UNIQUE (username) with field name;-- car add uniqueness constraint ALTER TABLE user12 ADD CONSTRAINT symbol UNIQUE KEY uni_card (card);-- View index SHOW CREATE TABLE user12;-- test,test1 to add federation uniqueALTER TABLE user12 ADD CONSTRAINT symbol UNIQUE INDEX mulUni_test_test1 (test,test1);-- Delete uniqueness-- ALTER TABLE tb_name DROP {INDEX | KEY} index_name -- delete the unique index ALTER TABLE user12 DROP INDEX username;ALTER TABLE user12 DROP KEY uni_card;ALTER TABLE user12 DROP KEY mulUni_test_test1 that you just added

Modify the storage engine of the table:

-- modify the storage engine of the table-- ALTER TABLE tb_name ENGINE= storage engine name ALTER TABLE user12 ENGINE=MyISAM;ALTER TABLE user12 ENGINE=INNODB

Modify the self-growth value:

-- modify self-increasing value-- ALTER TABLE tb_name AUTO_INCREMENT= value ALTER TABLE user12 AUTO_INCREMENT=100; after reading this article, I believe you have some understanding of how to modify mysql columns. If you want to know more about it, welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.