mysql將資料匯入到excel中,mysqlexcel

來源:互聯網
上載者:User

mysql將資料匯入到excel中,mysqlexcel

先建立一張測試的資料表

create table users(

 username varchar(40) not null,

 password varchar(40) not null)charset=utf8;

插入資料

insertinto users values("admin","admin");

insertinto users values("理央silence","王培坤");

使用下面的語句將資料庫中的資料寫入test.xls檔案中

mysql>select username,password from users into outfile "d:\\test.xls";

(注意,這裡mysql對該檔案的路徑要有寫入的許可權)

(或者使用下面的語句匯出到excel中select* into outfile "d:\\test1.xls" from test;)

因為office預設的是gb2312編碼,而伺服器使用的可能是utf-8編碼,那麼excel檔案可能會出現亂碼,如下:


我們可以給某個欄位進行重新編碼,如下:

mysql>select convert(username using gb2312),convert(password using gb2312) from

 users into outfile "d:\\test.xls";

這是檔案中的正常顯示:


mysql> select convert(username using gb2312),convert(password using gb2312)from

 users into outfile "d:\\test.xls";

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.