Mysql SQL statement operation to add or modify primary key

Add table fields

alter table table1 add transactor varchar(10) not Null;
alter table table1 add id int unsigned not Null auto_increment primary key

Modify the field type of a table and specify it as empty or non-empty

alter table table namechange field namefield namefield type[whether non-empty is allowed];

alter table table name modify field name field type [whether non-empty is allowed];

Modify the field name of a table and specify it as empty or non-empty

alter table table name change field original name field new name field type [whether non-empty is allowed

Delete a field

ALTER TABLE mytable DROP column name;

Adding a unique key

ALTER TABLE `test2` ADD UNIQUE ( `userid`)

Modify the primary key

ALTER TABLE `test2` DROP PRIMARY KEY ,ADD PRIMARY KEY ( `id` )

Adding Index

ALTER TABLE `test2` ADD INDEX ( `id` )
ALTER TABLE `category` MODIFY COLUMN `id` int(11) NOT NULL AUTO_INCREMENT FIRST ,ADD PRIMARY KEY (`id`);

The SQL statement block for modifying the primary key is as follows

New fields in the mailbox table

DROP PROCEDURE IF EXISTS mailbox_column_update;
CREATE PROCEDURE mailbox_column_update() BEGIN
 -- Add a delete flag column IF NOT EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='cbs' AND table_name='mailbox' AND COLUMN_NAME='delete_flag') THEN
   ALTER TABLE mailbox ADD delete_flag int DEFAULT 2 NOT NULL;
 END IF;
 -- Add a new deletion date column IF NOT EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='cbs' AND table_name='mailbox' AND COLUMN_NAME='delete_date') THEN
   ALTER TABLE mailbox ADD delete_date int DEFAULT 0 NOT NULL;
 END IF;
 -- If the field account_mail exists, modify the field length IF EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='cbs' AND table_name='mailbox' AND COLUMN_NAME='email_account')
 THEN
  alter table mailbox modify column email_account varchar(320);
 END IF;
 -- If there is no primary key column, set a double primary keyIF ((SELECT count(*) FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA ='cbs' AND table_name='mailbox' AND CONSTRAINT_NAME ='PRIMARY' AND (COLUMN_NAME ='email_account' OR COLUMN_NAME = 'company_id'))=0)THEN
    ALTER TABLE mailbox ADD primary key (company_id,email_account);
 -- If there is only one primary key column ELSEIF ((SELECT count(*) FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA ='cbs' AND table_name='mailbox' AND CONSTRAINT_NAME ='PRIMARY' AND (COLUMN_NAME ='email_account' OR COLUMN_NAME = 'company_id'))<2) THEN
    ALTER TABLE mailbox DROP PRIMARY KEY,ADD primary key (company_id,email_account);
 END IF;
  
END;
CALL mailbox_column_update();
DROP PROCEDURE IF EXISTS mailbox_column_update;

Supplement: mysql modifies the primary key to increase automatically, and adds a joint primary key

ALTER TABLE `onduty_history`
MODIFY COLUMN `id` int(11) NOT NULL AUTO_INCREMENT FIRST ,
MODIFY COLUMN `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL AFTER `id`,
MODIFY COLUMN `onduty_date` datetime NOT NULL AFTER `name`,
ADD UNIQUE KEY (`id`),
ADD PRIMARY KEY (`name`, `onduty_date`);

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.

You may also be interested in:

In-depth discussion on auto-increment primary keys in MySQL
The difference and advantages and disadvantages of Mysql primary key UUID and auto-increment primary key
MySQL primary key naming strategy related
What to do if the auto-increment primary key in MySQL is used up
Solution to running out of MySQL's auto-increment ID (primary key)
In-depth analysis of why MySQL does not recommend using uuid or snowflake id as primary key
Detailed explanation of non-primary key column overflow monitoring in MySQL tables
Use prometheus to count the remaining available percentage of MySQL auto-increment primary keys

<<: Use nginx to dynamically convert image sizes to generate thumbnails

>>: Detailed explanation of Vue's front-end system and front-end and back-end separation