MySQL 4.0 升级到mysql 5.0的方法_MySQL
bitsCN.com
一、从 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中这方面的特性
在MySQL4.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数值型(FLOAD, DOUBLE, DECIMAL) 这两种类型的变化；另外，我升级过程中暂时还不需要涉及到字符集问题，因此相对轻松一些。
升级步骤如下：
执行
FLUSH TABLES WITH READ LOCK;/[code]
直接拷贝 MyISAM 表文件
用 mysqldump 导出 Innodb 类型的表
整个过程都很顺利，新系统启动之后，发现如下2个问题：
新增了关键字 INOUT，因此需要检查表结构中还有其他什么字段使用关键字了
DATE_FORMAT 函数要求严谨多了，
[code]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 类型也没问题，剩下的就是检查其他的了。bitsCN.com
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
Detailed explanation of the method of converting int type to string in PHP
Mar 26, 2024 am 11:45 AM
Detailed explanation of the method of converting int type to string in PHP In PHP development, we often encounter the need to convert int type to string type. This conversion can be achieved in a variety of ways. This article will introduce several common methods in detail, with specific code examples to help readers better understand. 1. Use PHP’s built-in function strval(). PHP provides a built-in function strval() that can convert variables of different types into string types. When we need to convert int type to string type,
How to determine whether a Golang string ends with a specified character
Mar 12, 2024 pm 04:48 PM
Title: How to determine whether a string ends with a specific character in Golang. In the Go language, sometimes we need to determine whether a string ends with a specific character. This is very common when processing strings. This article will introduce how to use the Go language to implement this function, and provide code examples for your reference. First, let's take a look at how to determine whether a string ends with a specified character in Golang. The characters in a string in Golang can be obtained through indexing, and the length of the string can be
How to check if a string starts with a specific character in Golang?
Mar 12, 2024 pm 09:42 PM
How to check if a string starts with a specific character in Golang? When programming in Golang, you often encounter situations where you need to check whether a string begins with a specific character. To meet this requirement, we can use the functions provided by the strings package in Golang to achieve this. Next, we will introduce in detail how to use Golang to check whether a string starts with a specific character, with specific code examples. In Golang, we can use HasPrefix from the strings package
How to repeat a string in python_python repeating string tutorial
Apr 02, 2024 pm 03:58 PM
1. First open pycharm and enter the pycharm homepage. 2. Then create a new python script, right-click - click new - click pythonfile. 3. Enter a string, code: s="-". 4. Then you need to repeat the symbols in the string 20 times, code: s1=s*20. 5. Enter the print output code, code: print(s1). 6. Finally run the script and you will see our return value at the bottom: - repeated 20 times.
How to solve the problem of Chinese garbled characters when converting hexadecimal to string in PHP
Mar 04, 2024 am 09:36 AM
Methods to solve Chinese garbled characters when converting hexadecimal strings in PHP. In PHP programming, sometimes we encounter situations where we need to convert strings represented by hexadecimal into normal Chinese characters. However, in the process of this conversion, sometimes you will encounter the problem of Chinese garbled characters. This article will provide you with a method to solve the problem of Chinese garbled characters when converting hexadecimal to string in PHP, and give specific code examples. Use the hex2bin() function for hexadecimal conversion. PHP’s built-in hex2bin() function can convert 1
PHP String Matching Tips: Avoid Ambiguous Included Expressions
Feb 29, 2024 am 08:06 AM
PHP String Matching Tips: Avoid Ambiguous Included Expressions In PHP development, string matching is a common task, usually used to find specific text content or to verify the format of input. However, sometimes we need to avoid using ambiguous inclusion expressions to ensure match accuracy. This article will introduce some techniques to avoid ambiguous inclusion expressions when doing string matching in PHP, and provide specific code examples. Use preg_match() function for exact matching In PHP, you can use preg_mat
PHP string manipulation: a practical way to effectively remove spaces
Mar 24, 2024 am 11:45 AM
PHP String Operation: A Practical Method to Effectively Remove Spaces In PHP development, you often encounter situations where you need to remove spaces from a string. Removing spaces can make the string cleaner and facilitate subsequent data processing and display. This article will introduce several effective and practical methods for removing spaces, and attach specific code examples. Method 1: Use the PHP built-in function trim(). The PHP built-in function trim() can remove spaces at both ends of the string (including spaces, tabs, newlines, etc.). It is very convenient and easy to use.
PHP techniques for deleting the last two characters of a string
Mar 23, 2024 pm 12:18 PM
As a scripting language widely used to develop web applications, PHP has very powerful string processing functions. In daily development, we often encounter operations that require deleting a string, especially the last two characters of the string. This article will introduce two PHP techniques for deleting the last two characters of a string and provide specific code examples. Tip 1: Use the substr function The substr function in PHP is used to return a part of a string. We can easily remove characters by specifying the string and starting position
