创建表

import pymysql

conn = pymysql.connect(host='127.0.0.1',
                       user='root',
                       password='123456',
                       port=3306,
                       database='blog')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
affect_rows = cursor.execute('''
create table `user_info` (
    `id` bigint unsigned not null auto_increment,
    `name` varchar(45) not null default '',
    primary key (`id`)
) engine = InnoDB default charset = utf8mb4;
''')

print(affect_rows)  # 输出0

cursor.close()
conn.close()

给表添加索引

import pymysql

conn = pymysql.connect(host='127.0.0.1',
                       user='root',
                       password='123456',
                       port=3306,
                       database='blog')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
affect_rows = cursor.execute('''
ALTER TABLE `user_info` ADD INDEX idx_name(`name`); 
''')

print(affect_rows)  # 输出0

cursor.close()
conn.close()

执行后，通过mysql客户端查询表的创建语句，会发现多了新的索引：

mysql> show create table user_info\G
*************************** 1. row ***************************
       Table: user_info
Create Table: CREATE TABLE `user_info` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

Python PyMySQL：添加索引

创建表

给表添加索引