search
HomeDatabaseMysql TutorialMySQL 4.0 升级到5.0

由于需要,从4.0直接升级到5.0,查看了一下changelog,发现主要有以下变化: 一、从 4.0 到 4.1 的主要变化 如果在4.1.0到4.1.3版本的MySQL中创建了包含 TIMESTAMP 字段的 InnoDB 表。则在升级到4.1.4及更高时需要重建表,因为存储格式发生变化了 字符串根据

由于需要,从4.0直接升级到5.0,查看了一下changelog,发现主要有以下变化:

一、从 4.0 到 4.1 的主要变化

  • 如果在4.1.0到4.1.3版本的MySQL中创建了包含 TIMESTAMP 字段的 InnoDB
    表。则在升级到4.1.4及更高时需要重建表,因为存储格式发生变化了
  • 字符串根据标准SQL来比较:比较之前不删除末尾的空格,以前用末尾空格扩展了比较短的字符串。现在的结果是
    'a' > 'a/t',以前则不这样。可以用 mysqlcheck 来检查一下数据表
  • TIMESTAMP 返回 'YYYY-MM-DD HH:MM:SS' 格式的字符串。在MySQL
    4.0中,可以增加选项 --new 来获得MySQL 4.1中这方面的特性
  • 在MySQL
    4.1.1前,语句解析器不是那么严格,它在处理字符串转时间转换时会忽略第一个数字前的其他字符。在4.1.1之后,就比较严格了
  • 返回结果是 DATE, DATETIME, 或 TIME 类型的函数的结果会被转换成时间型

二、再看从 4.1 到 5.0 的主要变化

  • InnoDB 和 MyISAM 表中空格结尾的 TEXT 字段索引顺序改变了。因此需要运行
    "CHECK TABLE" 语句修复数据表,如果出现错误,就运行 "OPTIMIZE TABLE" 或 "REPAIR
    TABLE" 语句修复,甚至重新转储(用mysqldump)
  • MySQL 5.0.15开始,如何处理 BINARY 字段中填充的值已经改变了。填充的值现在是
    0x00 而非空格了,并且在取值的时候不会去除末尾的空格
  • 从MySQL 5.0.3开始,DECIMAL 的实现方式已经改变了,5.0对 DECIMAL
    的格式限制严格多了
  • 在MySQL 5.0.3到5.0.5之间版本的 MyISAM 和 InnoDB 表中创建的 DECIMAL
    字段升级到5.0.6之后会发生崩溃
  • 在以前,等待超时的锁会导致 InnoDB
    回滚当前全部事务,从5.0.13开始,就只回滚最近的SQL语句了
  • 在4.1.13/5.0.8以前,DATETIME 的加0后就转换成 YYYYMMDDHHMMSS 格式,现在变成
    YYYYMMDDHHMMSS.000000 格式了
  • 从5.0.3开始,DECIMAL 用更有效的格式来存储
  • 5.0.3开始,在计算 DECIMAL 值和舍入精确值的时候采用精确数学
  • 4.1中,FLOAT 或 DOUBLE 之间的比较碰巧没问题,但在5.0中可能就不行了
  • 从5.0.3开始,VARCHAR 和 VARBINARY 字段中末尾的空格不再删除
  • 增加了一个新的启动选项 innodb_table_locks,它导致 LOCK TABLE 时也可以请求
    InnoDB 表锁。这个选项默认打开,不过可能在 AUTOCOMMIT=1 和 LOCK TABLES
    应用中会导致死锁

看来,我只需主要关注 时间(TIMESTAMP, DATETIME) 和<br> 数值型(<code>FLOAD, DOUBLE, DECIMAL) 这两种类型的变化;另外,我升级过程中暂时还不需要涉及到字符集问题,因此相对轻松一些。

升级步骤如下:

  • 执行
    FLUSH TABLES WITH READ LOCK;

    直接拷贝 MyISAM 表文件

  • mysqldump 导出 Innodb 类型的表

整个过程都很顺利,新系统启动之后,发现如下2个问题:

  • 新增了关键字 INOUT,因此需要检查表结构中还有其他什么字段使用关键字了
  • DATE_FORMAT 函数要求严谨多了,
    DATE_FORMAT('2006/11/24 09:14:00', '%Y-%m-%d %T')

    DATE_FORMAT('2006/11/2409:14:00', '%Y-%m-%d %T')

    的结果完全不一样,在 4.0 中,能兼容这两种格式,而在 5.0 中,只能正确的使用前者了,后者则会有问题。这也应该是上面提到的时间类型发生的变化所致。

到此为止,升级基本结束,大致检查了 DECIMAL 类型也没问题,剩下的就是检查其他的了。


Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
What are stored procedures in MySQL?What are stored procedures in MySQL?May 01, 2025 am 12:27 AM

Stored procedures are precompiled SQL statements in MySQL for improving performance and simplifying complex operations. 1. Improve performance: After the first compilation, subsequent calls do not need to be recompiled. 2. Improve security: Restrict data table access through permission control. 3. Simplify complex operations: combine multiple SQL statements to simplify application layer logic.

How does query caching work in MySQL?How does query caching work in MySQL?May 01, 2025 am 12:26 AM

The working principle of MySQL query cache is to store the results of SELECT query, and when the same query is executed again, the cached results are directly returned. 1) Query cache improves database reading performance and finds cached results through hash values. 2) Simple configuration, set query_cache_type and query_cache_size in MySQL configuration file. 3) Use the SQL_NO_CACHE keyword to disable the cache of specific queries. 4) In high-frequency update environments, query cache may cause performance bottlenecks and needs to be optimized for use through monitoring and adjustment of parameters.

What are the advantages of using MySQL over other relational databases?What are the advantages of using MySQL over other relational databases?May 01, 2025 am 12:18 AM

The reasons why MySQL is widely used in various projects include: 1. High performance and scalability, supporting multiple storage engines; 2. Easy to use and maintain, simple configuration and rich tools; 3. Rich ecosystem, attracting a large number of community and third-party tool support; 4. Cross-platform support, suitable for multiple operating systems.

How do you handle database upgrades in MySQL?How do you handle database upgrades in MySQL?Apr 30, 2025 am 12:28 AM

The steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.

What are the different backup strategies you can use for MySQL?What are the different backup strategies you can use for MySQL?Apr 30, 2025 am 12:28 AM

MySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.

What is MySQL clustering?What is MySQL clustering?Apr 30, 2025 am 12:28 AM

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

How do you optimize database schema design for performance in MySQL?How do you optimize database schema design for performance in MySQL?Apr 30, 2025 am 12:27 AM

Optimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.

How can you optimize MySQL performance?How can you optimize MySQL performance?Apr 30, 2025 am 12:26 AM

TooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

SublimeText3 English version

SublimeText3 English version

Recommended: Win version, supports code prompts!

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.