Jak zmienić sortowanie bazy danych, tabeli, kolumny?

Baza danych jest latin1_general_citeraz i chcę zmienić sortowanie na utf8mb4_general_ci.

Czy w PhpMyAdmin jest jakieś ustawienie zmieniające sortowanie bazy danych, tabeli, kolumny? Zamiast zmieniać jeden po drugim?

Musisz przekonwertować każdą tabelę osobno:

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 

(spowoduje to również konwersję kolumn) lub wyeksportuje bazę danych latin1i zaimportuje ją z powrotem utf8mb4.

Przyczyniam się tutaj, jak OP poprosił:

Jak zmienić sortowanie bazy danych, tabeli, kolumny?

Wybrana odpowiedź podaje ją tylko na poziomie stołu.

Zmiana całej bazy danych:

ALTER DATABASE <database_name> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Zmiana według tabeli:

ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Dobrą praktyką jest zmiana go na poziomie tabeli, ponieważ zmieni się on również dla kolumn. Zmiana na konkretną kolumnę dotyczy dowolnego konkretnego przypadku.

Zmienianie sortowania dla określonej kolumny:

ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Możesz uruchomić skrypt php.

               <?php
                   $con = mysql_connect('localhost','user','password');
                   if(!$con) { echo "Cannot connect to the database ";die();}
                   mysql_select_db('dbname');
                   $result=mysql_query('show tables');
                   while($tables = mysql_fetch_array($result)) {
                            foreach ($tables as $key => $value) {
                             mysql_query("ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
                       }}
                   echo "The collation of your database has been successfully changed!";
                ?>

Aby zmienić sortowanie tabel indywidualnie, możesz użyć,

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8

Aby ustawić domyślne sortowanie dla całej bazy danych,

ALTER DATABASE  `databasename` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin

albo,

Idź do PhpMyAdmin-> Operacje-> Sortowanie.

Tam znajdziesz pole wyboru, które zawiera wszystkie wychodzące zestawienia. Abyś mógł tutaj zmienić swoje zestawienie. Tak więc tutaj po tabeli bazy danych nastąpi śledzenie tego sortowania podczas tworzenia nowej kolumny. Nie trzeba wybierać sortowania podczas tworzenia nowych kolumn.

Poniższe zapytanie wygeneruje zapytania ALTER, które zmieniają sortowanie dla wszystkich odpowiednich kolumn we wszystkich tabelach na określony typ (utf8_general_ci w moim przykładzie poniżej).

SELECT concat
        (
            'ALTER TABLE ', 
                t1.TABLE_SCHEMA, 
                '.', 
                t1.table_name, 
                ' MODIFY ', 
                t1.column_name, 
                ' ', 
                t1.data_type, 
                '(' , 
                    CHARACTER_MAXIMUM_LENGTH, 
                ')', 
                ' CHARACTER SET utf8 COLLATE utf8_general_ci;'
        )
from 
    information_schema.columns t1
where 
    t1.TABLE_SCHEMA like 'you_db_name_goes_here' AND
    t1.COLLATION_NAME IS NOT NULL AND
    t1.COLLATION_NAME NOT IN ('utf8_general_ci');

Jeśli uruchomisz phpMyAdmin >> wybierz bazę danych >> wybierz tabelę >> przejdź do zakładki „Operacje” >> w sekcji „Opcje tabeli” >> możesz wybrać Sortowanie z rozwijanej listy >> i po naciśnięciu {Go} na u góry ekranu zobaczysz komunikat:

Twoje zapytanie SQL zostało wykonane pomyślnie

i skrypt

ALTER TABLE `tableName` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci 

Ale to NIE zmieni zestawień istniejących kolumn. Aby to zrobić, możesz użyć tego skryptu (ten również pochodzi z phpMyAdmin)

ALTER TABLE  `tableName` CHANGE  `Name`  `Name` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL

możesz ustawić domyślne sortowanie na kilku poziomach:

http://dev.mysql.com/doc/refman/5.0/en/charset-syntax.html

1) klient 2) domyślny serwer 3) domyślna baza danych 4) domyślna tabela 5) kolumna

Wystarczy uruchomić ten SQL, aby przekonwertować wszystkie tabele bazy danych jednocześnie. Zmień COLLATION i databaseName na to, czego potrzebujesz.

SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," COLLATE utf8_general_ci;") AS    ExecuteTheString
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="databaseName"
AND TABLE_TYPE="BASE TABLE";

Możesz zmienić CHARSET i COLLATION wszystkich swoich tabel za pomocą skryptu PHP w następujący sposób. Podoba mi się odpowiedź hkasera, ale problem polega na tym, że zapytanie jest uruchamiane dwukrotnie w każdej tabeli. Ten kod jest prawie taki sam, z wyjątkiem używania MySqli zamiast mysql i zapobiegania podwójnym zapytaniom. Gdybym mógł głosować, głosowałbym za odpowiedzią hkasery.

<?php
$conn1=new MySQLi("localhost","user","password","database");
if($conn1->connect_errno){
    echo mysqli_connect_error();
    exit;
}
$res=$conn1->query("show tables") or die($conn1->error);
while($tables=$res->fetch_array()){
    $conn1->query("ALTER TABLE $tables[0] CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci") or die($conn1->error);
}
echo "The collation of your database has been successfully changed!";

$res->free();
$conn1->close();

?>

Możesz po prostu dodać ten kod do pliku skryptu

//Database Connection
$host = 'localhost';
$db_name = 'your_database_name';
$db_user =  'your_database_user_name';
$db_pass = 'your_database_user_password';

$con = mysql_connect($host,$db_user,$db_pass);

if(!$con) { echo "Cannot connect to the database ";die();}

  mysql_select_db($db_name);

  $result=mysql_query('show tables');

  while($tables = mysql_fetch_array($result)) {
    foreach ($tables as $key => $value) {
    mysql_query("ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
  }
}

echo "The collation of your database has been successfully changed!";

Zaskoczyło mnie to, dlatego musiałem tu wrócić i zgłosić, że doskonały i dobrze utrzymany skrypt Interconnect / it SAFE SEARCH AND REPLACE ON DATABASE ma kilka opcji konwersji tabel na utf8 / unicode, a nawet konwersji na innodb . Jest to skrypt powszechnie używany do migracji strony internetowej opartej na bazie danych (Wordpress, Drupal, Joomla itp.) Z jednej domeny do drugiej.

• https://github.com/interconnectit/Search-Replace-DB
• https://interconnectit.com/products/search-and-replace-for-wordpress-databases/

Przeczytałem tutaj, że musisz przekonwertować każdą tabelę ręcznie, to nieprawda. Oto rozwiązanie, jak to zrobić za pomocą procedury składowanej:

DELIMITER $$

DROP PROCEDURE IF EXISTS changeCollation$$

-- character_set parameter could be 'utf8'
-- or 'latin1' or any other valid character set
CREATE PROCEDURE changeCollation(IN character_set VARCHAR(255))
BEGIN
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE v_table_name varchar(255) DEFAULT "";
DECLARE v_message varchar(4000) DEFAULT "No records";

-- This will create a cursor that selects each table,
-- where the character set is not the one
-- that is defined in the parameter

DECLARE alter_cursor CURSOR FOR SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
AND COLLATION_NAME NOT LIKE CONCAT(character_set, '_%');

-- This handler will set the value v_finished to 1
-- if there are no more rows

DECLARE CONTINUE HANDLER
FOR NOT FOUND SET v_finished = 1;

OPEN alter_cursor;

-- Start a loop to fetch each rows from the cursor
get_table: LOOP

-- Fetch the table names one by one
FETCH alter_cursor INTO v_table_name;

-- If there is no more record, then we have to skip
-- the commands inside the loop
IF v_finished = 1 THEN
LEAVE get_table;
END IF;

IF v_table_name != '' THEN

IF v_message = 'No records' THEN
SET v_message = '';
END IF;

-- This technic makes the trick, it prepares a statement
-- that is based on the v_table_name parameter and it means
-- that this one is different by each iteration inside the loop

SET @s = CONCAT('ALTER TABLE ',v_table_name,
' CONVERT TO CHARACTER SET ', character_set);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET v_message = CONCAT('The table ', v_table_name ,
' was changed to the default collation of ', character_set,
'.\n', v_message);

SET v_table_name = '';

END IF;
-- Close the loop and the cursor
END LOOP get_table;
CLOSE alter_cursor;

-- Returns information about the altered tables or 'No records'
SELECT v_message;

END $$

DELIMITER ;

Po utworzeniu procedury wywołaj ją po prostu:

CALL changeCollation('utf8');

Aby uzyskać więcej informacji, przeczytaj ten blog .

jeśli chcesz zaktualizować domyślny zestaw znaków w schemacie:

 ALTER SCHEMA MYSCHEMA DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci;

Użyłem następującego skryptu powłoki. Pobiera nazwę bazy danych jako parametr i konwertuje wszystkie tabele na inny zestaw znaków i zestawianie (podane przez inne parametry lub wartość domyślną zdefiniowaną w skrypcie).

#!/bin/bash

# mycollate.sh <database> [<charset> <collation>]
# changes MySQL/MariaDB charset and collation for one database - all tables and
# all columns in all tables

DB="$1"
CHARSET="$2"
COLL="$3"

[ -n "$DB" ] || exit 1
[ -n "$CHARSET" ] || CHARSET="utf8mb4"
[ -n "$COLL" ] || COLL="utf8mb4_general_ci"

echo $DB
echo "ALTER DATABASE $DB CHARACTER SET $CHARSET COLLATE $COLL;" | mysql

echo "USE $DB; SHOW TABLES;" | mysql -s | (
    while read TABLE; do
        echo $DB.$TABLE
        echo "ALTER TABLE $TABLE CONVERT TO CHARACTER SET $CHARSET COLLATE $COLL;" | mysql $DB
    done
)

Moje rozwiązanie to połączenie @Dzintars i @Quassnoi Answer.

SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 ;") AS    ExecuteTheString
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="<your-database>"
AND TABLE_TYPE="BASE TABLE";

Za pomocą CONVERT TOtego generuje skrypty, które konwertują wszystkie tabele <your-database>na żądane kodowanie. To również zmienia kodowanie każdej kolumny !

Lepszy wariant do generowania skryptu SQL na żądanie SQL. Nie zrujnuje wartości domyślnych / zerowych.

SELECT concat
    (
        'ALTER TABLE ', 
            t1.TABLE_SCHEMA, 
            '.', 
            t1.table_name, 
            ' MODIFY ', 
            t1.column_name, 
            ' ', 
            t1.column_type,
            ' CHARACTER SET utf8 COLLATE utf8_general_ci',
            if(t1.is_nullable='YES', ' NULL', ' NOT NULL'),
            if(t1.column_default is not null, concat(' DEFAULT \'', t1.column_default, '\''), ''),
            ';'
    )
from 
    information_schema.columns t1
where 
    t1.TABLE_SCHEMA like 'your_table_here' AND
    t1.COLLATION_NAME IS NOT NULL AND
    t1.COLLATION_NAME NOT IN ('utf8_general_ci');

Szybki sposób - wyeksportuj do pliku SQL, użyj wyszukiwania i zamień, aby zmienić tekst, który chcesz zmienić. Utwórz nową bazę danych, zaimportuj dane, a następnie zmień nazwę starej bazy danych i nowej na starą nazwę.

Aby zmienić układanie wszystkich pól we wszystkich tabelach bazy danych jednocześnie:

Właśnie dodałem kolejną pętlę dla pól w tabelach do rozwiązania za pośrednictwem Php wcześniej wspomnianego. Pomogło to, wszystkie pola w tabelach są również konwertowane.

<?php
$con = mysql_connect('localhost','user','pw');
if(!$con) { echo "Cannot connect to the database ";die();}
mysql_select_db('database_name');
$result=mysql_query('show tables');
while($tables = mysql_fetch_array($result)) {

foreach ($tables as $key => $table) {                   // for each table

    $sql = "ALTER TABLE $table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci";
    echo "\n".$sql;
    mysql_query($sql);

    $sql = "show fields in ".$table." where type like 'varchar%' or type like 'char%' or type='text' or type='mediumtext';";
    $rs2=mysql_query($sql);
    while( $rw2 = mysql_fetch_array($rs2) ){            // for each field in table

        $sql = "ALTER TABLE `".$table."` CHANGE `".$rw2['Field']."` `".$rw2['Field']."` ".$rw2['Type']." CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;";
        echo "\n".$sql;
        mysql_query($sql);

    } 


}
}
echo "The collation of your database has been successfully changed!";

?>}

Właśnie napisałem skrypt bash, aby znaleźć wszystkie tabele w danej bazie danych i ukryć je (i jego kolumny).

Skrypt jest dostępny tutaj: https://github.com/Juddling/mysql-charset