Reference blog

Tag: phpmyadmin

  • phpmyadmin: mysql export Simplified Chinese utf csv gibberish text issue

    The database collation is utf8_unicode_ci but can’t guarantee the Chinese text encoding was 100% correct during data entry (may have mixed with Traditional Chinese?)

    When we use the usual route in phpmyadmin to export the data to .csv, the generated .csv file doesn’t show the Simplified Chinese text properly. Same issue when exporting to Microsoft .csv

    Manual workaround: Export the data to .csv but select “View output as text”. This somehow generates the data with the Simplified Chinese text correctly.

    Select all, copy and paste to your Microsoft Excel and… problem solved.

  • mysql: How to copy a database in phpMyAdmin

    How to copy a database in phpMyAdmin

    This tutorial will demonstrate how to copy or duplicate a database in phpMyAdmin.

    Begin by accessing phpMyAdmin via cPanel .

    Select the database you wish to copy (by clicking on the database from the phpMyAdmin home screen).
    Once inside the database, select the Operations tab.
    Scroll down to the section where it says “Copy database to:”
    Type in the name of the new database.
    Select “structure and data” to copy everything. Alternately, you can select “Structure only” if you want the columns but not the data.
    Check the box “CREATE DATABASE before copying” to create a new database.
    Check the box “Add AUTO_INCREMENT value.”
    Click on the Go button to proceed.

    If you click on the home icon, you will notice your new database listed in the left column. If you go inside, you will see the database is a copy of the previous database.
    Source: https://my.bluehost.com/cgi/help/2154

  • mysql: Change character encoding for database and tables

    http://stackoverflow.com/questions/6115612/how-to-convert-an-entire-mysql-database-characterset-and-collation-to-utf-8

    Use the ALTER DATABASE and ALTER TABLE commands.

    ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
    ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

    If not possible,
    Export structure and data with phpmyadmin
    Custom – display all possible options
    Database system or older MySQL server to maximize output compatibility with: MYSQL40
    Syntax to use when inserting data: include column names in every INSERT statement (Example: INSERT INTO tbl_name (col_A,col_B,col_C) VALUES (1,2,3))
    After export, remove all TYPE=InnoDB from the text file (.sql)
    Create new database in utf8_unicode_ci;
    Import the .sql file and tables created will inherit utf8_unicode_ci.