创建表PAY_SERIAL_202104
脚本准备：
create table PAY_SERIAL_202104
(
PAY_SERIAL_ID varchar(50) not null,
PAY_NODE_ID decimal(9,0) not null,
NODE_PAY_METHOD_ID decimal(9,0) not null,
PAY_CHANNEL_SERIAL varchar(50),
PAY_USER_SERIAL varchar(50) not null,
AMOUNT decimal(16,0) not null,
OPER_SERIAL_ID varchar(50),
OPER_SERIAL_NBR varchar(50),
STATUS_CD decimal(2,0) not null,
CREATE_DATE datetime not null,
PAY_DATE datetime not null,
STATUS_DATE datetime not null,
TRADE_TYPE_ID decimal(9,0) not null,
NOTIFY_URL varchar(250),
RETURN_URL varchar(250),
POS_SETTLE_SERIAL varchar(50),
POS_TERMIALNBR varchar(50),
POS_PAY_SERIAL varchar(50),
CHANNEL_RETURN_CODE varchar(32),
CHANNEL_RETURN_MSG varchar(250),
STAFF_ID decimal(16,0) not null,
COMMODITY_NAME varchar(250),
COMMODITY_DESC varchar(250),
ACCOUNT_ID decimal(16,0),
CUSTOMER_ACCOUNT varchar(50),
REGION_ID decimal(9,0),
CUST_ID varchar(64) not null,
PAY_TYPE_ID decimal(9,0) not null,
acc_num varchar(64),
REAL_AMOUNT decimal(16,0) not null,
ORG_ID decimal(16,0) not null,
PAY_USER_NOTIFY_URL varchar(256) comment '支付中心调用使用方链接进行通知,
PAY_METHOD_TYPE decimal(3,0) not null default -1 comment '支付渠道类型,
PAY_CHANNEL_TYPE decimal(12,0) not null default -1 comment '支付渠道类型,
OBJ_ATTR varchar(10) not null default ‘NUL’ comment '来自服务对象条件（vcObjectStruct）。:固话1:小灵通2:移动3:宽带4:智能公话5:互联星空6:天翼高清99:未知,
OBJ_TYPE varchar(10) not null default ‘NUL’ comment '来自服务对象条件（vcObjectStruct）。:帐户标识2:用户标识3:用户号码4:客户标识5:销售品实例,
DATA_AREA varchar(10) not null default ‘NUL’ comment '来自服务对象条件（vcObjectStruct）。:按帐户范围2:按用户范围3:按客户范围4:按销售品范围,
IS_MATCHING decimal(2,0) not null default 0 comment '是否匹配对帐文件：默认状态1：匹配过对帐记录-对帐填写2 无需对帐直接送使用端系统的对帐数据 -采集进程填写,
BAR_AUTH_CODE bigint(20) comment '支付条码。,
CHECK_RESULT decimal(2,0) not null default 0 comment '对帐结果。:未处理默认值；1:支付渠道有，使用方无；2:支付渠道无，使用方有；3:两方都有，但金额不对；4:对帐正常-此字段对帐回填5:渠道确认失败不对帐8:使用方已平账,
ALLOCATION_AREA_CODE decimal(12,0) comment '对应REA_CODE_COMMON_REGION_REL. AREA_CODE—6个县分帐的县编号,
COMMON_REGION_ID decimal(12,0) comment '使用方传入的原始值,
FEE_AMOUNT decimal(16,0) comment '手续费单位：分,
MERCH_ID varchar(32) comment '商户编号,
store_id varchar(50) comment '门店编号,
app_id varchar(50) comment ‘APPID’,
CURRENCY_TYPE_ID numeric(6,0),
part_id int(3),
primary key (PAY_SERIAL_ID)
);
create index IDX_PAYSERIAL_CHANNEL_SERIAL on PAY_SERIAL_202104
(
PAY_CHANNEL_SERIAL
);
create index IDX_PAYSERIAL_CREATE_DATE on PAY_SERIAL_202104
(
CREATE_DATE
);
create index IDX_PAYSERIAL_PAY_NODE_ID on PAY_SERIAL_202104
(
PAY_NODE_ID
);
create index IDX_PAYSERIAL_STATUS_CD on PAY_SERIAL_202104
(
STATUS_CD
);
create index IDX_PAYSERIAL_USER_SERIAL on PAY_SERIAL_202104
(
PAY_USER_SERIAL
);
执行创建表的操作
mysql> use pto_project_test;
Database changed
mysql> create table PAY_SERIAL_202104
-> (
-> PAY_SERIAL_ID varchar(50) not null,
-> PAY_NODE_ID decimal(9,0) not null,
-> NODE_PAY_METHOD_ID decimal(9,0) not null,
-> PAY_CHANNEL_SERIAL varchar(50),
-> PAY_USER_SERIAL varchar(50) not null,
-> AMOUNT decimal(16,0) not null,
-> OPER_SERIAL_ID varchar(50),
-> OPER_SERIAL_NBR varchar(50),
-> STATUS_CD decimal(2,0) not null,
-> CREATE_DATE datetime not null,
-> PAY_DATE datetime not null,
-> STATUS_DATE datetime not null,
-> TRADE_TYPE_ID decimal(9,0) not null,
-> NOTIFY_URL varchar(250),
-> RETURN_URL varchar(250),
-> POS_SETTLE_SERIAL varchar(50),
-> POS_TERMIALNBR varchar(50),
-> POS_PAY_SERIAL varchar(50),
-> CHANNEL_RETURN_CODE varchar(32),
-> CHANNEL_RETURN_MSG varchar(250),
-> STAFF_ID decimal(16,0) not null,
-> COMMODITY_NAME varchar(250),
-> COMMODITY_DESC varchar(250),
-> ACCOUNT_ID decimal(16,0),
-> CUSTOMER_ACCOUNT varchar(50),
-> REGION_ID decimal(9,0),
-> CUST_ID varchar(64) not null,
-> PAY_TYPE_ID decimal(9,0) not null,
-> acc_num varchar(64),
-> REAL_AMOUNT decimal(16,0) not null,
-> ORG_ID decimal(16,0) not null,
-> PAY_USER_NOTIFY_URL varchar(256) comment ‘支付中心调用使用方链接进行 通知’,
-> PAY_METHOD_TYPE decimal(3,0) not null default -1 comment ‘支付渠 道类型’,
-> PAY_CHANNEL_TYPE decimal(12,0) not null default -1 comment ‘支付渠道类型’,
-> OBJ_ATTR varchar(10) not null default ‘NUL’ comment ‘来自 服务对象条件（SvcObjectStruct）。0:固话;1:小灵通;2:移动;3:宽带;4:智能公话;5:互联星空;6:天翼高清;99:未知’,
-> OBJ_TYPE varchar(10) not null default ‘NUL’ comment ‘来自 服务对象条件（SvcObjectStruct）。1:帐户标识;2:用户标识;3:用户号码;4:客户标识;5: 销售品实例’,
-> DATA_AREA varchar(10) not null default ‘NUL’ comment ‘来自 服务对象条件（SvcObjectStruct）。1:按帐户范围;2:按用户范围;3:按客户范围;4:按销售品范围’,
-> IS_MATCHING decimal(2,0) not null default 0 comment ‘是否匹配对帐文件0： 默认状态,1：匹配过对帐记录 --对帐填写,2 无需对帐(直接送使用端系统的 对帐数据) -采集进程填写’,
-> BAR_AUTH_CODE bigint(20) comment ‘支付条码。’,
-> CHECK_RESULT decimal(2,0) not null default 0 comment ‘对帐结果。0:未处理(默认值)； ;1:支付渠道有，使用方无 ；;2:支付渠道无，使用方有 ；;3:两方都有，但金额不对；;4:对帐正常 --此字段对帐回填;5:渠道确认失败不对帐;8:使用方已平账’,
-> ALLOCATION_AREA_CODE decimal(12,0) comment ‘对应AREA_CODE_COMMON_REGION_REL. AREA_CODE—96个县分帐的县编号’,
-> COMMON_REGION_ID decimal(12,0) comment ‘使用方传入的原始值’,
-> FEE_AMOUNT decimal(16,0) comment ‘手续费单位：分’,
-> MERCH_ID varchar(32) comment ‘商户编号’,
-> store_id varchar(50) comment ‘门店编号’,
-> app_id varchar(50) comment ‘APPID’,
-> CURRENCY_TYPE_ID numeric(6,0),
-> part_id int(3),
-> primary key (PAY_SERIAL_ID)
-> );
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql>
mysql> create index IDX_PAYSERIAL_CHANNEL_SERIAL on PAY_SERIAL_202104
-> (
-> PAY_CHANNEL_SERIAL
-> );
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> create index IDX_PAYSERIAL_CREATE_DATE on PAY_SERIAL_202104
-> (
-> CREATE_DATE
-> );
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> create index IDX_PAYSERIAL_PAY_NODE_ID on PAY_SERIAL_202104
-> (
-> PAY_NODE_ID
-> );
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> create index IDX_PAYSERIAL_STATUS_CD on PAY_SERIAL_202104
-> (
-> STATUS_CD
-> );
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> create index IDX_PAYSERIAL_USER_SERIAL on PAY_SERIAL_202104
-> (
-> PAY_USER_SERIAL
-> );
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
导入csv数据到mysql
mysql> load data infile ‘/Users/candy/Desktop/mysql/pay_serial_202104.csv’ into table PAY_SERIAL_202104;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
mysql> show global variables like ‘%secure_file_priv%’;
±-----------------±------+
| Variable_name | Value |
±-----------------±------+
| secure_file_priv | NULL |
±-----------------±------+
1 row in set (0.09 sec)
mysql> set global secure_file_priv=’’;
ERROR 1238 (HY000): Variable ‘secure_file_priv’ is a read only variable
secure_file_prive=null – 限制mysqld 不允许导入导出
secure_file_priv=/Users/candy/Desktop/mysql/ – 限制mysqld的导入导出只能发生在/tmp/目录下
secure_file_priv=’ ’ – 不对mysqld 的导入 导出做限制
EthanNi-MacBook-Pro.local.err 日志：
mysqld: File ‘./binlog.index’ not found (OS errno 13 - Permission denied)
2021-04-23T06:27:00.702806Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.24) starting as process 61006
2021-04-23T06:27:00.707640Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive
2021-04-23T06:27:00.715145Z 0 [ERROR] [MY-010119] [Server] Aborting
2021-04-23T06:27:00.715448Z 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.24) MySQL Community Server - GPL.
经检查./binlog.index文件的的所属用户不是mysql，固无权限。
sudo chown -R mysql:mysql /usr/local/mysql/data
EthanNi-MacBook-Pro:data candy$ sudo /usr/local/mysql/support-files/mysql.server start
Starting MySQL
… SUCCESS!
EthanNi-MacBook-Pro.local.err 日志：
2021-04-23T06:39:12.849833Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.24) starting as process 61223
2021-04-23T06:39:12.943359Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive
2021-04-23T06:39:13.048332Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-04-23T06:39:13.625281Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-04-23T06:39:13.919513Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: ‘::’ port: 33060, socket: /tmp/mysqlx.sock
2021-04-23T06:39:14.102172Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-04-23T06:39:14.103523Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-04-23T06:39:14.116008Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location ‘/usr/local/mysql/data’ in the path is accessible to all OS users. Consider choosing a different directory.
2021-04-23T06:39:14.177984Z 0 [System] [MY-010931] [Server] /usr/local/mysql/bin/mysqld: ready for connections. Version: ‘8.0.24’ socket: ‘/tmp/mysql.sock’ port: 3306 MySQL Community Server - GPL.
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
在/etc/里面新建一个，然后加入一行 secure_file_priv=’’
修改后重启mysql，
mysql> show global variables like ‘%secure_file_priv%’;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect…
Connection id: 8
Current database: pto_project_test
±-----------------±----------------------------+
| Variable_name | Value |
±-----------------±----------------------------+
| secure_file_priv | |
±-----------------+——————————————+
1 rows in set (0.02 sec)
似乎没有exit退出，session会自动重连。
mysql> load data infile ‘/Users/candy/Desktop/mysql/pay_serial_202104.csv’ into table PAY_SERIAL_202104;
ERROR 13 (HY000): Can’t get stat of ‘/Users/candy/Desktop/mysql/pay_serial_202104.csv’ (OS errno 13 - Permission denied)
mysql没这个文件夹的权限，把文件cp到/usr/local/mysql/data/：
mysql> load data infile ‘/usr/local/mysql/data/pay_serial_202104.csv’ into table PAY_SERIAL_202104 FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘"’ LINES TERMINATED BY ‘\n’;
ERROR 1366 (HY000): Incorrect decimal value: ‘PAY_NODE_ID’ for column ‘PAY_NODE_ID’ at row 1
怀疑是第一行是列名，不符合表列的字符类型，想办法导入的时候skip row1的数据
mysql> help load data
Name: ‘LOAD DATA’
Description:
Syntax:
LOAD DATA
[LOW_PRIORITY | CONCURRENT] [LOCAL]
INFILE ‘file_name’
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (partition_name [, partition_name] …)]
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY ‘string’]
[[OPTIONALLY] ENCLOSED BY ‘char’]
[ESCAPED BY ‘char’]
]
[LINES
[STARTING BY ‘string’]
[TERMINATED BY ‘string’]
]
[IGNORE number {LINES | ROWS}]
[(col_name_or_user_var
[, col_name_or_user_var] …)]
[SET col_name={expr | DEFAULT}
[, col_name={expr | DEFAULT}] …]
The LOAD DATA statement reads rows from a text file into a table at a
very high speed. LOAD DATA is the complement of SELECT … INTO
OUTFILE. (See
https://dev.mysql.com/doc/refman/8.0/en/select-into.html.) To write
data from a table to a file, use SELECT … INTO OUTFILE. To read the
file back into a table, use LOAD DATA. The syntax of the FIELDS and
LINES clauses is the same for both statements.
You can also load data files by using the mysqlimport utility; see
https://dev.mysql.com/doc/refman/8.0/en/mysqlimport.html. mysqlimport
operates by sending a LOAD DATA statement to the server.
For more information about the efficiency of INSERT versus LOAD DATA
and speeding up LOAD DATA, see
https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html.
URL: https://dev.mysql.com/doc/refman/8.0/en/load-data.html
https://dev.mysql.com/doc/refman/8.0/en/load-data.html里面看见：
LOAD DATA can be used to read files obtained from external sources. For example, many programs can export data in comma-separated values (CSV) format, such that lines have fields separated by commas and enclosed within double quotation marks, with an initial line of column names. If the lines in such a file are terminated by carriage return/newline pairs, the statement shown here illustrates the field- and line-handling options you would use to load the file:
LOAD DATA INFILE ‘data.txt’ INTO TABLE tbl_name
FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘"’
LINES TERMINATED BY ‘\r\n’
IGNORE 1 LINES;
mysql> load data infile ‘/usr/local/mysql/data/pay_serial_202104.csv’ into table PAY_SERIAL_202104 FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘"’ LINES TERMINATED BY ‘\n’ ignore 1 rows;
ERROR 1292 (22007): Incorrect datetime value: ‘1/4/2021 07:30:51’ for column ‘CREATE_DATE’ at row 1
查阅文档得知mysql的默认datetime格式是：‘0000-00-00 00:00:00’
alter table PAY_SERIAL_202104 modify column CREATE_DATE varchar(50);
alter table PAY_SERIAL_202104 modify column PAY_DATE varchar(50);
alter table PAY_SERIAL_202104 modify column STATUS_DATE varchar(50);
mysql> load data infile ‘/usr/local/mysql/data/pay_serial_202104.csv’ into table PAY_SERIAL_202104 FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘"’ LINES TERMINATED BY ‘\n’ ignore 1 lines;
ERROR 1366 (HY000): Incorrect decimal value: ‘’ for column ‘ACCOUNT_ID’ at row 1
似乎excel的内容不能为空， 可以通过python把为空的列replace为0或者null。或者导入的时候加nullif函数。
不过，额，TIME is limited,
所以来个曲线救国吧？只导第一列和第3列吧，其他设置为默认。这个表有52列。需要50个@dummy
mysql> load data infile ‘/usr/local/mysql/data/pay_serial_202104.csv’ into table PAY_SERIAL_202104 FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘"’ LINES TERMINATED BY ‘\n’ ignore 1 rows
-> (PAY_SERIAL_ID, @dummy,@dummy, @dummy, @dummy,@dummy, @dummy, @dummy,STATUS_CD,@dummy, @dummy, @dummy,@dummy, @dummy, @dummy,@dummy, @dummy, @dummy,@dummy, @dummy, @dummy,@dummy, @dummy, @dummy,@dummy, @dummy, @dummy,@dummy, @dummy, @dummy,@dummy, @dummy, @dummy,@dummy, @dummy, @dummy,@dummy, @dummy, @dummy,@dummy, @dummy, @dummy,@dummy, @dummy, @dummy,@dummy, @dummy, @dummy,@dummy,@dummy,@dummy, @dummy);
Query OK, 21325 rows affected (1.10 sec)
Records: 21325 Deleted: 0 Skipped: 0 Warnings: 0
哇哇哇！Fantanstic，一万数据才1秒！！！！比PLSQL工具导入强多了！！！！！！！！老子对账的曙光来了。
另外如果文件的列和load data命令的列，数量不等，会返回如下报错：
ERROR 1262 (01000): Row 1 was truncated; it contained more data than there were input columns
使用主键约束
分为用单个列作为主键，
还有一个叫联合主键，多个列作为主键，
使用场景，多个列组合保持记录的唯一性，比如brand，model。一个匹配的型号是唯一的，但是同一个型号，不同匹配可能一样。
其他外键约束，非空约束，唯一性约束，默认约束（设置列的默认值）
另外primary主键不能为null，唯一性约束可以。
auto_increment
oracle的create sequence序列，mysql不支持。
不过可以支持表的列的属性来实现自动生成序列。
另外auto_increment,只能作用于主键，默认从1开始。
oracle版本为了迁移到mysql，
公司框架那边可以封装了一个功能，屏蔽了，mysql和oracle的底层细节。比如以前oracle的sequence 统一插入TFM_SEQUENCES表。
查看表的结构
desc table_name;
describe table_name;
mysql> desc pto_project_test. pay_serial_202104;
±---------------------±--------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±---------------------±--------------±-----±----±--------±------+
| PAY_SERIAL_ID | varchar(50) | NO | PRI | NULL | |
| PAY_NODE_ID | decimal(9,0) | NO | MUL | NULL | |
NULL列，YES表述可以为空。
Key列表示该列是否有索引，PRI表示主键索引，MUL表示该列值不唯一
Extra表示列的额外信息，比如是否是auto_increment.
查看建表语句
mysql> show create table pay_serial_202104 \G;
*************************** 1. row ***************************
Table: pay_serial_202104
Create Table: CREATE TABLE pay_serial_202104 (
PAY_SERIAL_ID varchar(50) NOT NULL,
加上\G显示的时候会更直观。
其他修改列名，增加、删除列就不说了。跟oracle差不多。
存储引擎
显示mysql支持的引擎信息。
mysql> show engines;
可以看到表的默认引起是Inno DB
修改表的引擎：
alter table xxxx engine=MyISAM;
然后通过show create table语句看修改结果。
mysql 8.0新特性之 默认字符集改为utf8mb4
mysql 8.0之前默认是latin1，现在默认是
character_set_database 是 utf8mb4
mysql> show variables like ‘char%’ ;
±-------------------------±-------------------------------------------------------+
| Variable_name | Value |
±-------------------------±-------------------------------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/local/mysql-8.0.24-macos11-x86_64/share/charsets/ |
±-------------------------±-------------------------------------------------------+
规避了以前的乱码问题。
mysql 8.0新特性之 自增变量的持久化
mysql 8.0之前，当auto_increment列的值，大于 max(primary key)+1,
当mysql重启后，auto_increment的值会重置为max(primary key)+1。
比如重启前
当payment_id最大值10，然后删除了payment_id为10的值，
此时max(primary key)+1依旧是10，重启后如果新增一条记录，
payment_id会是10，
会导致his表等主键冲突或者其他的业务问题。
因为mysql5.7，auto_increment的规则是有InnoDB数据字段的一个计数器决定的，而计算器是在内存中维护的，不会持久化到磁盘。
mysql重启时，会取物理库max(primary key) 作为初始值。
mysql 8.0将自增变量持久化到redo日志中。
如果数据库重启，InnoDB会基于redo日志中的信息来初始化计数器的内存值。
660
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
