taibeihacker
Moderator
一、导入mysql数据前期设置
1.建庫和表統一編碼設置為UTF8，根據數據中的編碼來修改（也可以將數據全部轉換為utf-8格式，小文件可用notepad來修改編碼，大文件可用LogViewPro 來修改編碼）
2.對MySql數據庫進行優化配置
my.ini優化配置：
[mysql]
default-character-set=utf8
[mysqld]
port=3306
basedir=F:/phpstudy_pro/Extensions/MySQL5.7.26/
datadir=F:/phpstudy_pro/Extensions/MySQL5.7.26/data/
character-set-server=utf8 #默認的數據庫編碼
default-storage-engine=MyIsam #數據庫引擎，myisam適合於查詢
max_connections=1000 #客戶端和服務器最大連接數，默認為1000
collation-server=utf8_unicode_ci
init_connect='SET NAMES utf8'
innodb_buffer_pool_size=4096M #一般設置buffer pool 大小為總內存的3/4 至4/5
innodb_flush_log_at_trx_commit=2 #當設置為2，該模式速度較快，也比0安全，只有在操作系統崩潰或者係統斷電的情況下，上一秒鐘所有事務數據才可能丟失。
innodb_lock_wait_timeout=120 #默認參數:innodb_lock_wait_timeout設置鎖等待的時間是120s，一旦數據庫鎖超過這個時間就會報錯。
innodb_log_buffer_size=16M #建議取值16M-64MB，自己內存為8G
innodb_log_file_size=256M #一般取256M可以兼顧性能和recovery的速度，不可取大也不可取小
interactive_timeout=120 #服務器關閉交互式連接前等待活動的秒數
join_buffer_size=16M #聯合查詢操作所能使用的緩衝區大小，如果有100個線程連接，則佔用為16M*100
key_buffer_size=512M #索引緩衝區，一般情況下對於內存在4GB 左右的服務器該參數可設置為256M 或384M
log_error_verbosity=2 #錯誤日誌記錄內容
max_allowed_packet=128M #限制Server接受的數據包大小，默認是128M
max_heap_table_size=64M #設置默認值
myisam_max_sort_file_size=64G ## mysql重建索引時允許使用的臨時文件最大大小，默認值即可
myisam_sort_buffer_size=150M #MyISAM表發生變化時重新排序所需的緩衝
read_buffer_size=512kb #緩存連續掃描的塊，這個緩存是跨存儲引擎的，不只是MyISAM表，8G內存，建議是512KB
read_rnd_buffer_size=4M #MySql的隨機讀緩衝區大小建議末日使者
server_id=1
skip-external-locking=on #跳過外部鎖定
sort_buffer_size=256kb #排序緩衝
table_open_cache=3000
thread_cache_size=16
tmp_table_size=64M
wait_timeout=120
secure-file-priv='' #可在任意目錄下導入
log-error='F:/phpstudy_pro/Extensions/MySQL5.7.26/data'
[client]
port=3306
default-character-set=utf8
二、各种数据导入mysql方法
導入的數據類型有：sql數據，txt文本數據，cvs(xls)數據,以及access和mssql數據格式的數據1.txt文本格式數據導入
(1).txt體積不超過400M，超過的一律進行將其分割等分
(2)、合併txt文件，針對多個小文件的txt
合併txt文件的命令：
type *.txt all.txt（windows)
(3)、txt文件（使用tab間隔分割，回車換行）命令快速導入方式：
mysql -u root -p
use test;
load data infile 'J:/data/weibo/weibo/weibo_1.txt' into table weibo_info1 FIELDS TERMINATED BY '\t' lines terminated by '\r\n' (tel,uid);
注意：這裡導入的txt文件路徑為相對物理路徑，\t表示字段之間的分割符號為tab（空格），\r\n表示在windows系統下的數據的每行的換行符號
（4）、txt文件（使用----間隔分割，回車換行）命令快速導入方式：
load data infile 'E:/test.txt' into table test FIELDS TERMINATED BY '----' lines terminated by '\r\n' (tel,qq);
（5）、txt文件（使用,字符間隔分割，回車換行）命令快速導入方式：
load data infile 'E:/test.txt' into table test FIELDS TERMINATED BY ',' lines terminated by '\r\n' (tel,qq);
（6）、txt文本字段中含有雙引號字符的字段，會導致意外終止，這裡使用enclosed by命令來去掉雙引號
load data infile 'E:/test.txt' into table test FIELDS TERMINATED BY ',' enclosed by ''' lines terminated by '\r\n' (tel,qq);
（7）load data infile導入參數說明
fields terminated by ',' #表示字段數據之間用逗號分隔。
fields terminated by '\n' #表示每行數據之間的分隔符為換行符號(linux)
lines terminated by '\r\n' #表示每行數據之間的分隔符為換行符號(windows)
escaped by '' #表示對字段值中含有轉義字符的\進行刪除
enclosed by ''' #表示去掉字段值中的雙引號
(tel,qq) #表對應的字段名稱，這個需要和test.txt文件裡面的數據字段名稱對應
（8）、小txt文件（非tab的規律間隔符），可以使用操作簡單的Navicat導入數據
需要注意分割符合以及目標欄目中的數據對應導入的數據字段
(9)導入多個txt文件到mysql
想要批量導入txt文件，可以將通過批處理文件執行多條導入語句完成。
製作sql語句文件,可採用多種編程語言獲取要導入的txt文件名稱製成sql命令。
這裡採用python完成，建立python文件create_sql.py，示例代碼：(下面數據格式為----為間隔的數據）
import glob
writeFile=open('C:/Users/backlion/Desktop/data/user_sql.txt','w')
writeFile.write('use test;\n')
for filename in glob.glob(r'C:/Users/backlion/Desktop/data/*.txt'):
writeFile.write('load data local infile '+'''+filename.replace('\\','/')+'''+' into table user fields terminated by' + ''' + '----' + ''' + ' lines terminated by' + ''' + r'\r\n' + ''' + ';\n')
writeFile.close()
這樣就將data文件夾下的所有要導入的txt文件名稱製作成sql語句放在user_sql.txt中，內容大致如下：
D:\phpStudy\PHPTutorial\MySQL\bin\mysql.exe --local-infile -u root –proot C:/Users/backlion/Desktop/data/user_sql.txt
pause
2、csv文件導入到mysql
(1).單個cvs導入mysql，快速命令
mysql -u root -p
use test;
load data local infile 'C:/Users/backlion/Desktop/data/use1.csv' into table user fields terminated by',' lines terminated by'\r\n' (email,password);
（2）、多個cvs批量導入到mysql
想要批量導入txt文件，可以將通過批處理文件執行多條導入語句完成。
csv文件以','逗號作為分割符，需要用雙引號或者單引號括起來.
製作sql語句文件,可採用多種編程語言獲取要導入的txt文件名稱製成sql命令。
這裡採用python完成，建立python文件create_sql.py，示例代碼：(下面數據格式csv文件）
import glob
writeFile=open('C:/Users/backlion/Desktop/data/user_sql.txt','w')
writeFile.write('use test;\n')
for filename in glob.glob(r'C:/Users/backlion/Desktop/data/*.csv'):
writeFile.write('load data local infile '+'''+filename.replace('\\','/')+'''+' into table user fields terminated by' + ''' + ',' + ''' + ' lines terminated by' + ''' + r'\r\n' + ''' + ';\n')
writeFile.close()
這樣就將data文件夾下的所有要導入的txt文件名稱製作成sql語句放在user_sql.txt中，內容大致如下：
D:\phpStudy\PHPTutorial\MySQL\bin\mysql.exe --local-infile -u root –proot C:/Users/backlion/Desktop/data/user_sql.txt
pause
(3)多個csv文件合併
copy *.CSV all.csv
(3)、通過navicat導入cvs格式文件
2.sql格式導入mysql
(1)、單個sql格式的文件導入，不用考慮編碼問題，入庫後直接使用navicat編輯數據庫屬性轉UTF8編碼即可，然後再創建索引
使用命令：
use test;
source D:\test.sql;
在裡面寫入：
source 1.sql
source 2.sql
.
source 53.sql
source 54.sql
mysql source all.sql
(3)、多個sql文件合併
copy *.sql all.sql
(4)、通過navicat導入sql格式文件
三、导入技巧
1.統計MYSQL數據重複數量mysqlselect email, count(email) as count from user group by email having
mysqlcount(email) 1;
或者
SELECT * FROM user WHERE email IN (SELECT email FROM user GROUP BY email HAVING COUNT(email ) 1);
2、數據去重
mysql CREATE TABLE tmp SELECT email,password FROM user GROUP BY email,password ;
或者
mysql CREATE TABLE tmp SELECT email FROM user GROUP BY email;
mysql DROP TABLE user;
mysql ALTER TABLE tmp RENAME TO user;
1）選擇已選擇的字段或者主鍵值重複項中的記錄
create table new as (select email,password from user group by email,password having count(*)1);
2）創建索引（僅需在第一次時執行）
create index email on new(email) ;
3）刪除重複項中字段或者主鍵值的記錄
delete from user where email in (select email from new);
4）刪除臨時表
drop table new;
3、添加索引，並查詢優化
給常用查詢字段添加索引，模糊類用BTREE存儲類型，精確類用HASH存儲類型。推薦使用Navicat 選擇表打開表訊息，選擇DDL選項卡，可以清楚看到該表的sql，有無索引一目了然，然後右擊數據庫名選擇它的console功能，來快速添加索引。
5、xls和cvs等excle格式的文件，建議直接使用navicat編輯數據庫屬性轉UTF8編碼即可，然後再創建索引
6、先通過navicat創建數據庫和表和字段等數據庫結構後，然後創建索引，最後導入數據（這個是針對數據量很大的數據，如果先導入很大的數據，最後再來創建索引，會直接卡死，並且卡很久）
7.mssql導入到mysql數據庫中，通過navicat的導入功能中的mssql數據庫源導入
