查看建表语句
show create table tableName;
eg:
mysql> show create table demo;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| demo | CREATE TABLE `demo` (
`uid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`uname` varchar(500) DEFAULT NULL,
`email` varchar(500) DEFAULT NULL,
`createTime` datetime DEFAULT NULL,
PRIMARY KEY (`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)可以看到该表使用的数据引擎事 MyISAM (该引擎不支持事务)
查看可使用的数据引擎
show engines;
eg:
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
可以看到只有InnoDB引擎才支持事务
修改表的数据引擎
alter table tableName engine=innodb;
eg:
mysql> alter table demo engine=innodb;
Query OK, 1 row affected (0.58 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show create table demo;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| demo | CREATE TABLE `demo` (
`uid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`uname` varchar(500) DEFAULT NULL,
`email` varchar(500) DEFAULT NULL,
`createTime` datetime DEFAULT NULL,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)修改默认的存储引擎
修改my.cnf文件,在[mysqld]下加入:
default-storage-engine=INNODB
修改默认的字符集
修改my.cnf文件,在[mysqld]下加入:
# charset
default-character-set=utf8
default-collation=utf8_general_ci
character-set-server=utf8
collation-server=utf8_general_ci
init-connect='SET NAMES utf8'