MySQL
ÄNDRA TABELL

Syntax

• ALTER [IGNORE] TABELL tbl_name [ alter_specification [, alter_specification] ...] [partition_options]

Anmärkningar

    alter_specification: table_options
      | ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]
      | ADD [COLUMN] (col_name column_definition,...)
      | ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ...
      | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) [index_option] ...
      | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_option] ...
      | ADD FULLTEXT [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ...
      | ADD SPATIAL [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ...
      | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition
      | ALGORITHM [=] {DEFAULT|INPLACE|COPY}
      | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
      | CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
      | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
      | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
      | DROP [COLUMN] col_name
      | DROP PRIMARY KEY
      | DROP {INDEX|KEY} index_name
      | DROP FOREIGN KEY fk_symbol
      | DISABLE KEYS
      | ENABLE KEYS
      | RENAME [TO|AS] new_tbl_name
      | RENAME {INDEX|KEY} old_index_name TO new_index_name
      | ORDER BY col_name [, col_name] ...
      | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
      | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
      | DISCARD TABLESPACE
      | IMPORT TABLESPACE
      | FORCE
      | {WITHOUT|WITH} VALIDATION
      | ADD PARTITION (partition_definition)
      | DROP PARTITION partition_names
      | DISCARD PARTITION {partition_names | ALL} TABLESPACE
      | IMPORT PARTITION {partition_names | ALL} TABLESPACE
      | TRUNCATE PARTITION {partition_names | ALL}
      | COALESCE PARTITION number
      | REORGANIZE PARTITION partition_names INTO (partition_definitions)
      | EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH|WITHOUT} VALIDATION]
      | ANALYZE PARTITION {partition_names | ALL}
      | CHECK PARTITION {partition_names | ALL}
      | OPTIMIZE PARTITION {partition_names | ALL}
      | REBUILD PARTITION {partition_names | ALL}
      | REPAIR PARTITION {partition_names | ALL}
      | REMOVE PARTITIONING
      | UPGRADE PARTITIONING
   index_col_name: col_name [(length)] [ASC | DESC]
   index_type: USING {BTREE | HASH}
   index_option: KEY_BLOCK_SIZE [=] value
      | index_type
      | WITH PARSER parser_name
      | COMMENT 'string'

Byte av lagringsmotor; återuppbygga bordet; ändra file_per_table

Till exempel, om t1 närvarande inte är en InnoDB-tabell, ändrar detta uttalande sin lagringsmotor till InnoDB:

ALTER TABLE t1 ENGINE = InnoDB;

Om tabellen redan är InnoDB kommer detta att bygga upp tabellen och dess index och har en effekt som liknar OPTIMIZE TABLE . Det kan hända att du förbättrar hårddiskutrymmet.

Om värdet på innodb_file_per_table närvarande är annorlunda än det värde som gällde när t1 byggdes kommer det att konverteras till (eller från) file_per_table.

ÄNDRA KOLONN AV TABELL

CREATE DATABASE stackoverflow;

USE stackoverflow;

Create table stack(
    id_user int NOT NULL,
    username varchar(30) NOT NULL,
    password varchar(30) NOT NULL
);

ALTER TABLE stack ADD COLUMN submit date NOT NULL; -- add new column
ALTER TABLE stack DROP COLUMN submit; -- drop column
ALTER TABLE stack MODIFY submit DATETIME NOT NULL; -- modify type column
ALTER TABLE stack CHANGE submit submit_date DATETIME NOT NULL; -- change type and name of column
ALTER TABLE stack ADD COLUMN mod_id INT NOT NULL AFTER id_user; -- add new column after existing column

ALTER-tabell lägg till INDEX

För att förbättra prestanda kanske du vill lägga till index i kolumner

ALTER TABLE TABLE_NAME ADD INDEX `index_name` (`column_name`)

ändra för att lägga till kompositindex (flera kolumner)

ALTER TABLE TABLE_NAME ADD INDEX `index_name` (`col1`,`col2`)

Ändra automatisk inkrementvärde

Att ändra ett automatiskt inkrementvärde är användbart när du inte vill ha ett mellanrum i en AUTO_INCREMENT-kolumn efter en massiv radering.

Till exempel har du en massa oönskade rader (annons) i din tabell, du raderade dem och du vill fixa gapet i värden för automatisk inkrement. Antag att MAX-värdet för kolumnen AUTO_INCREMENT är 100 nu. Du kan använda följande för att fixa värdet för automatisk inkrement.

ALTER TABLE your_table_name AUTO_INCREMENT = 101;

Ändra typen av en primär nyckelkolumn

ALTER TABLE fish_data.fish DROP PRIMARY KEY;
ALTER TABLE fish_data.fish MODIFY COLUMN fish_id DECIMAL(20,0) NOT NULL PRIMARY KEY;

Ett försök att ändra typen av denna kolumn utan att först släppa den primära nyckeln skulle resultera i ett fel.

Ändra kolumndefinition

Ändra definitionen av en db-kolumn, frågan nedan kan användas till exempel om vi har detta db-schema

users (
    firstname varchar(20),
    lastname varchar(20),
    age char(2)
)

För att ändra typen av age från char till int använder vi frågan nedan:

ALTER TABLE users CHANGE age age tinyint UNSIGNED NOT NULL;

Allmänt format är:

ALTER TABLE table_name CHANGE column_name new_column_definition

Byt namn på en MySQL-databas

Det finns inget enda kommando för att byta namn på en MySQL-databas men en enkel lösning kan användas för att uppnå detta genom att säkerhetskopiera och återställa:

mysqladmin -uroot -p<password> create <new name>
mysqldump -uroot -p<password> --routines <old name> | mysql -uroot -pmypassword <new name>
mysqladmin -uroot -p<password> drop <old name>

Steg:

1. Kopiera raderna ovan till en textredigerare.
2. Byt ut alla referenser till <old name> , <new name> och <password> (+ valfritt root att använda en annan användare) med relevanta värden.
3. Kör en efter en på kommandoraden (förutsatt att mappen MySQL "bin" finns i sökvägen och ange "y" när du uppmanas).

Alternativa steg:

Byt namn på (flytta) varje tabell från en db till den andra. Gör detta för varje tabell:

RENAME TABLE `<old db>`.`<name>` TO  `<new db>`.`<name>`;

Du kan skapa dessa uttalanden genom att göra något liknande

SELECT CONCAT('RENAME TABLE old_db.', table_name, ' TO ',
                           'new_db.', table_name)
    FROM information_schema.TABLES
    WHERE table_schema = 'old_db';

Varning. Försök inte göra någon sorts tabell eller databas genom att bara flytta filer runt i filsystemet. Detta fungerade bra i gamla dagar för bara MyISAM, men under de nya dagarna för InnoDB och bordsytor fungerar det inte. Särskilt när "Data Dictionary" flyttas från filsystemet till system InnoDB-tabeller, förmodligen i nästa större utgåva. Att flytta (i motsats till bara DROPping ) en PARTITION av en InnoDB-tabell kräver att man använder "transportabla tabellområden". Inom en nära framtid kommer det inte ens att finnas en fil att nå fram till.

Byta namn på två MySQL-databaser

Följande kommandon kan användas för att byta namn på två MySQL-databaser ( <db1> och <db2> ):

mysqladmin -uroot -p<password> create swaptemp
mysqldump -uroot -p<password> --routines <db1> | mysql -uroot -p<password> swaptemp
mysqladmin -uroot -p<password> drop <db1>
mysqladmin -uroot -p<password> create <db1>
mysqldump -uroot -p<password> --routines <db2> | mysql -uroot -p<password> <db1>
mysqladmin -uroot -p<password> drop <db2>
mysqladmin -uroot -p<password> create <db2>
mysqldump -uroot -p<password> --routines swaptemp | mysql -uroot -p<password> <db2>
mysqladmin -uroot -p<password> drop swaptemp

Steg:

1. Kopiera raderna ovan till en textredigerare.
2. Byt ut alla referenser till <db1> , <db2> och <password> (+ valfritt root att använda en annan användare) med relevanta värden.
3. Kör en efter en på kommandoraden (förutsatt att mappen MySQL "bin" finns i sökvägen och ange "y" när du uppmanas).

Byt namn på en MySQL-tabell

Att byta namn på en tabell kan göras i ett enda kommando:

RENAME TABLE `<old name>` TO `<new name>`;

Följande syntax gör exakt samma sak:

ALTER TABLE `<old name>` RENAME TO `<new name>`;

Om man byter namn på en tillfällig tabell ALTER TABLE versionen av syntaxen användas.

Steg:

1. Byt ut <old name> och <new name> i raden ovan med relevanta värden. Obs: Om tabellen flyttas till en annan databas, dbname . tablename kan användas för <old name> och / eller <new name> .
2. Kör den på den relevanta databasen i kommandoraden MySQL eller en klient som MySQL Workbench. Obs: Användaren måste ha ALTER- och DROP-behörigheter på det gamla bordet och SKAPA och INSERT på det nya.

Byt namn på en kolumn i en MySQL-tabell

Att byta namn på en kolumn kan göras i ett enda uttalande men liksom det nya namnet, måste "kolumndefinitionen" (dvs. dess datatyp och andra valfria egenskaper såsom nollbarhet, automatisk inkrementering etc.) också specificeras.

ALTER TABLE `<table name>` CHANGE `<old name>` `<new name>` <column definition>;

Steg:

1. Öppna kommandoraden MySQL eller en klient som MySQL Workbench.
2. Kör följande uttalande: SHOW CREATE TABLE <table name>; (ersätter <table name> med relevant värde).
3. Skriv en notering av hela kolumndefinitionen för kolumnen som ska byta namn (dvs. allt som visas efter kolumnens namn men före komma som separerar den från nästa kolumnnamn) .
4. Byt ut <old name> , <new name> och <column definition> i raden ovan med relevanta värden och kör sedan det.