Last active
July 27, 2018 07:24
-
-
Save CleanCoder/d126465d9e6406c6567890580d0bb53d to your computer and use it in GitHub Desktop.
MySQL
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 索引 | |
1)索引必须按照“idx_字段名称_字段名称[_字段名]”进行命名。 | |
2)唯一索引必须按照“uniq_字段名称_字段名称[_字段名]”进行命名。 | |
3)名称必须使用小写。 | |
4)中的字段数建议不超过5个,索引毕竟也占空间,请谨慎使用 | |
5)主键是最左边一列是id,id不做业务使用,建表的时候必须有索引,不建议创建这个结构的表 | |
# Create Table If Not Exists | |
CREATE TABLE IF NOT EXISTS `shares` ( | |
`id` int(64) NOT NULL auto_increment, | |
`file_id` int(64) NOT NULL, | |
`user_id` int(64) NOT NULL, | |
`shared` tinyint(4) NOT NULL, | |
PRIMARY KEY (`id`) | |
) ; | |
# Add Column If Not Exist | |
SET @dbname = DATABASE(); | |
SET @tablename = "tableName"; | |
SET @columnname = "colName"; | |
SET @preparedStatement = (SELECT IF( | |
( | |
SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS | |
WHERE | |
(table_name = @tablename) | |
AND (table_schema = @dbname) | |
AND (column_name = @columnname) | |
) > 0, | |
"SELECT 1", | |
CONCAT("ALTER TABLE ", @tablename, " ADD ", @columnname, " INT(11);") | |
)); | |
PREPARE alterIfNotExists FROM @preparedStatement; | |
EXECUTE alterIfNotExists; | |
DEALLOCATE PREPARE alterIfNotExists; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment