DB-Dump: Anpassung eines Datensatzes an den Zeichensatz COLLATE utf8mb4_unicode_ci
Verfasst: Samstag 19. März 2022, 17:15
Hallo und guten Abend Community,
.... ich hab also eine Anpassung nötig bzgl. des Zeichenstatzes -
Hintergrund; ich will einen DB-Dump in eine Msql-DB einpflegen - der Dump hat einen speziellen Zeichensatz - und schaut so aus:
Und so geht das dann über 2000 Zeilen:
... ergo: ich hab also eine Anpassung nötig bzgl. des Zeichenstatzes:
Also die Anpassung an meine DB - mit dem Zeichensatz utf8mb4 werde ich vornehmen in dem ich alles ersetze
was so ausschaut: utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
mit : DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
oder anders gesagt:
damit das dann passt zur DB - hier ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
also ich mache praktisch einen konsequenten Austausch der DATEN - von
nach
im gesamten DB-Dump... !? Das müsste so gehen, denke ich mal.
Update: das allerdings reicht noch nicht. Ich muss och mehr suchen.
hier ein Auszug aus meiner DB:
Fazit: Also - ich denke dass ich mich hauptsächlich um die collation kümmern muss - Der charset ist ja schon utfmb4
Muss mal sehen, was ich also konkret alles ändern muss.
Freue mich von Euch zu hören. VG
.... ich hab also eine Anpassung nötig bzgl. des Zeichenstatzes -
Hintergrund; ich will einen DB-Dump in eine Msql-DB einpflegen - der Dump hat einen speziellen Zeichensatz - und schaut so aus:
Code: Alles auswählen
-- MySQL dump 10.13 Distrib 5.7.23-23, for Linux (x86_64)
--
-- Host: localhost Database: sunoje_wp868
-- ------------------------------------------------------
-- Server version 5.7.23-23
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!50717 SELECT COUNT(*) INTO @rocksdb_has_p_s_session_variables FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'performance_schema' AND TABLE_NAME = 'session_variables' */;
/*!50717 SET @rocksdb_get_is_supported = IF (@rocksdb_has_p_s_session_variables, 'SELECT COUNT(*) INTO @rocksdb_is_supported FROM performance_schema.session_variables WHERE VARIABLE_NAME=\'rocksdb_bulk_load\'', 'SELECT 0') */;
/*!50717 PREPARE s FROM @rocksdb_get_is_supported */;
/*!50717 EXECUTE s */;
/*!50717 DEALLOCATE PREPARE s */;
/*!50717 SET @rocksdb_enable_bulk_load = IF (@rocksdb_is_supported, 'SET SESSION rocksdb_bulk_load = 1', 'SET @rocksdb_dummy_bulk_load = 0') */;
/*!50717 PREPARE s FROM @rocksdb_enable_bulk_load */;
/*!50717 EXECUTE s */;
/*!50717 DEALLOCATE PREPARE s */;
DROP TABLE IF EXISTS `wpsu_actionscheduler_actions`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wpsu_actionscheduler_actions` (
`action_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`hook` varchar(191) COLLATE utf8mb4_unicode_520_ci NOT NULL,
`status` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL,
`scheduled_date_gmt` datetime DEFAULT '0000-00-00 00:00:00',
`scheduled_date_local` datetime DEFAULT '0000-00-00 00:00:00',
`args` varchar(191) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
`schedule` longtext COLLATE utf8mb4_unicode_520_ci,
`group_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`attempts` int(11) NOT NULL DEFAULT '0',
`last_attempt_gmt` datetime DEFAULT '0000-00-00 00:00:00',
`last_attempt_local` datetime DEFAULT '0000-00-00 00:00:00',
`claim_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`extended_args` varchar(8000) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
PRIMARY KEY (`action_id`),
KEY `hook` (`hook`),
KEY `status` (`status`),
KEY `scheduled_date_gmt` (`scheduled_date_gmt`),
KEY `args` (`args`),
KEY `group_id` (`group_id`),
KEY `last_attempt_gmt` (`last_attempt_gmt`),
KEY `claim_id_status_scheduled_date_gmt` (`claim_id`,`status`,`scheduled_date_gmt`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `wpsu_actionscheduler_actions`
--
LOCK TABLES `wpsu_actionscheduler_actions` WRITE;
/*!40000 ALTER TABLE `wpsu_actionscheduler_actions` DISABLE KEYS */;
INSERT INTO `wpsu_actionscheduler_actions` (`action_id`, `hook`, `status`, `scheduled_date_gmt`, `scheduled_date_local`, `args`, `schedule`, `group_id`, `attempts`, `last_attempt_gmt`, `last_attempt_local`, `claim_id`, `extended_args`) VALUES (7,'action_scheduler/migration_hook','complete','2022-02-24 18:34:17','2022-02-24 18:34:17','[]','O:30:\"ActionScheduler_SimpleSchedule\":2:{s:22:\"\0*\0scheduled_timestamp\";i:1645727657;s:41:\"\0ActionScheduler_SimpleSchedule\0timestamp\";i:1645727657;}',1,1,'2022-02-24 18:34:53','2022-02-24 18:34:53',0,NULL),(8,'woocommerce_admin/stored_state_setup_for_products/async/run_remote_notifications','complete','0000-00-00 00:00:00','0000-00-00 00:00:00','[]','O:28:\"ActionScheduler_NullSchedule\":0:{}',0,1,'2022-02-24 18:40:15','2022-02-24 18:40:15',0,NULL),(9,'woocommerce_admin/stored_state_setup_for_products/async/run_remote_notifications','complete','0000-00-00 00:00:00','0000-00-00 00:00:00','[]','O:28:\"ActionScheduler_NullSchedule\":0:{}',0,1,'2022-02-24 18:40:15','2022-02-24 18:40:15',0,NULL),(10,'woocommerce_admin/stored_state_setup_for_products/async/run_remote_notifications','complete','0000-00-00 00:00:00','0000-00-00 00:00:00','[]','O:28:\"ActionScheduler_NullSchedule\":0:{}',0,1,'2022-02-24 18:41:03','2022-02-24 18:41:03',0,NULL),(11,'woocommerce_admin/stored_state_setup_for_products/async/run_remote_notifications','complete','0000-00-00 00:00:00','0000-00-00 00:00:00','[]','O:28:\"ActionScheduler_NullSchedule\":0:{}',0,1,'2022-02-24 18:41:03','2022-02-24 18:41:03',0,NULL),(12,'woocommerce_admin/stored_state_setup_for_products/async/run_remote_notifications','complete','0000-00-00 00:00:00','0000-00-00 00:00:00','[]','O:28:\"ActionScheduler_NullSchedule\":0:{}',0,1,'2022-02-24 18:41:03','2022-02-24 18:41:03',0,NULL),(13,'woocommerce_admin/stored_state_setup_for_products/async/run_remote_notifications','complete','0000-00-00 00:00:00','0000-00-00 00:00:00','[]','O:28:\"ActionScheduler_NullSchedule\":0:{}',0,1,'2022-02-24 18:41:03','2022-02-24 18:41:03',0,NULL),(14,'woocommerce_admin/stored_state_setup_for_products/async/run_remote_notifications','complete','0000-00-00 00:00:00','0000-00-00 00:00:00','[]','O:28:\"ActionScheduler_NullSchedule\":0:{}',0,1,'2022-02-24 18:41:03','2022-02-24 18:41:03',0,NULL),(15,'woocommerce_admin/stored_state_setup_for_products/async/run_remote_notifications','complete','0000-00-00 00:00:00','0000-00-00 00:00:00','[]','O:28:\"ActionScheduler_NullSchedule\":0:{}',0,1,'2022-02-24 18:41:03','2022-02-24 18:41:03',0,NULL),(16,'woocommerce_admin/stored_state_setup_for_products/async/run_remote_notifications','complete','0000-00-00 00:00:00','0000-00-00 00:00:00','[]','O:28:\"ActionScheduler_NullSchedule\":0:{}',0,1,'2022-02-24 18:41:03','2022-02-24 18:41:03',0,NULL),(17,'woocommerce_run_update_callback','complete','2022-03-15 18:00:27','2022-03-15 18:00:27','{\"update_callback\":\"wc_update_630_create_product_attributes_lookup_table\"}','O:30:\"ActionScheduler_SimpleSchedule\":2:{s:22:\"\0*\0scheduled_timestamp\";i:1647367227;s:41:\"\0ActionScheduler_SimpleSchedule\0timestamp\";i:1647367227;}',2,1,'2022-03-15 18:00:47','2022-03-15 18:00:47',0,NULL),(18,'woocommerce_run_update_callback','complete','2022-03-15 18:00:28','2022-03-15 18:00:28','{\"update_callback\":\"wc_update_630_db_version\"}','O:30:\"ActionScheduler_SimpleSchedule\":2:{s:22:\"\0*\0scheduled_timestamp\";i:1647367228;s:41:\"\0ActionScheduler_SimpleSchedule\0timestamp\";i:1647367228;}',2,1,'2022-03-15 18:00:48','2022-03-15 18:00:48',0,NULL),(19,'woocommerce_update_db_to_current_version','complete','2022-03-15 18:00:29','2022-03-15 18:00:29','{\"version\":\"6.3.1\"}','O:30:\"ActionScheduler_SimpleSchedule\":2:{s:22:\"\0*\0scheduled_timestamp\";i:1647367229;s:41:\"\0ActionScheduler_SimpleSchedule\0timestamp\";i:1647367229;}',2,1,'2022-03-15 18:00:48','2022-03-15 18:00:48',0,NULL),(20,'woocommerce_run_product_attribute_lookup_regeneration_callback','complete','2022-03-15 18:00:48','2022-03-15 18:00:48','[]','O:30:\"ActionScheduler_SimpleSchedule\":2:{s:22:\"\0*\0scheduled_timestamp\";i:1647367248;s:41:\"\0ActionScheduler_SimpleSchedule\0timestamp\";i:1647367248;}',2,1,'2022-03-15 18:00:48','2022-03-15 18:00:48',0,NULL),(21,'wc-admin_import_customers','complete','2022-03-15 18:05:24','2022-03-15 18:05:24','[1]','O:30:\"ActionScheduler_SimpleSchedule\":2:{s:22:\"\0*\0scheduled_timestamp\";i:1647367524;s:41:\"\0ActionScheduler_SimpleSchedule\0timestamp\";i:1647367524;}',3,1,'2022-03-15 18:06:48','2022-03-15 18:06:48',0,NULL);
/*!40000 ALTER TABLE `wpsu_actionscheduler_actions` ENABLE KEYS */;
UNLOCK TABLES;Und so geht das dann über 2000 Zeilen:
... ergo: ich hab also eine Anpassung nötig bzgl. des Zeichenstatzes:
Also die Anpassung an meine DB - mit dem Zeichensatz utf8mb4 werde ich vornehmen in dem ich alles ersetze
was so ausschaut: utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
mit : DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
oder anders gesagt:
damit das dann passt zur DB - hier ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
also ich mache praktisch einen konsequenten Austausch der DATEN - von
Code: Alles auswählen
ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;Code: Alles auswählen
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;im gesamten DB-Dump... !? Das müsste so gehen, denke ich mal.
Update: das allerdings reicht noch nicht. Ich muss och mehr suchen.
hier ein Auszug aus meiner DB:
Code: Alles auswählen
-- MySQL dump 10.13 Distrib 5.5.33, for Linux (x86_64)
--
-- Host: localhost Database: fsj
-- ------------------------------------------------------
-- Server version 5.5.33
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `wp_postmeta`
--
DROP TABLE IF EXISTS `wp_postmeta`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wp_postmeta` (
`meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`post_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`meta_value` longtext COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (`meta_id`),
KEY `post_id` (`post_id`),
KEY `meta_key` (`meta_key`(191))
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `wp_postmeta`
--
LOCK TABLES `wp_postmeta` WRITE;
/*!40000 ALTER TABLE `wp_postmeta` DISABLE KEYS */;
INSERT INTO `wp_postmeta` VALUES (1,2,'_wp_page_template','default'),(2,3,'_wp_page_template','default');
/*!40000 ALTER TABLE `wp_postmeta` ENABLE KEYS */;
Fazit: Also - ich denke dass ich mich hauptsächlich um die collation kümmern muss - Der charset ist ja schon utfmb4
Muss mal sehen, was ich also konkret alles ändern muss.
Freue mich von Euch zu hören. VG
