mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)
创建表
mysql> create table brand(
-> bid varchar(9) not null, -> category varchar(10) not null, -> shop varchar(8) not null) -> engine=innodb -> default charset=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-----------------+| Tables_in_bigdb |
+-----------------+| brand |
+-----------------+
1 row in set (0.00 sec)
mysql> show create table brand;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| brand | CREATE TABLE `brand` (
`bid` varchar(9) NOT NULL, `category` varchar(10) NOT NULL, `shop` varchar(8) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
导入表
## 错误示例1 ##
mysql> load data infile '/home/bigdata/datasource/brand.list'
-> into table brand
-> fields terminated by ','
-> lines terminated by '¥n';
ERROR 13 (HY000): Can't get stat of '/home/bigdata/datasource/brand.list' (Errcode: 13)
## 错误示例2 ##
mysql> load local data infile '/home/bigdata/datasource/brand.list'
-> into table brand
-> fields terminated by ','
-> lines terminated by '¥n';
ERROR 1064 (42000): You have an errorin your SQL syntax; check the manual that corresponds to your MySQL server versionforthe right syntax to use near 'local data infile '/home/bigdata/datasource/brand.list'
into table brand
field' at line 1## 正确示例 ##
mysql> load data local infile '/home/bigdata/datasource/brand.list'
-> into table brand
-> fields terminated by ','
-> lines terminated by '\n';
Query OK, 1000 rows affected, 87 warnings (0.00 sec)
Records: 1000 Deleted: 0 Skipped: 0 Warnings: 87
mysql>
mysql> select * from brand limit 5;
+----------+-----------+----------+
| bid | category | shop |
+----------+-----------+----------+
| 00000000 | telephone | OPPO |
| 00000001 | computer | ASUS |
| 00000002 | sports | MIZUNO |
| 00000003 | sports | LINING |
+----------+-----------+----------+5 rows inset (0.00 sec)
mysql>