欢迎各位兄弟 发布技术文章
这里的技术是共享的
我在这里面临一点麻烦。
我做了一个新的迁移,将列从not null更新为null
php artisan make:migration update_foo_table --table foo
...和我的迁移文件...
$table->string('bar')->nullable()->change();
已经按照建议并要求doctrine/dbal (~2.4)
我的composer.json
文件下的包
事情是,当我尝试迁移时,我收到以下错误
exception 'Doctrine\DBAL\DBALException' with message 'Unknown column type "varchar(255)" requested. Any Doctrine type that you use has to be registered with \Doctrine\DBAL\Types\Type::addType(). You can get a list of all the known types with \Doctrine\DBAL\Types\Type::getTypesMap(). If this error occurs during database introspection then you might have forgot to register all database types for a Doctrine Type. Use AbstractPlatform#registerDoctrineTypeMapping() or have your custom types implement Type#getMappedDatabaseTypes(). If the type name is empty you might have a problem with the cache or forgot some mapping information.' in /home/vagrant/code/gestec-nit/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:228
我究竟做错了什么?
我假设您正在尝试编辑已经添加了数据的列,因此在不丢失数据的情况下,不可能删除列并再次添加为可空列。我们将改变现有的列。
假设你有这样的迁移:
Schema::create('users', function(Blueprint $table)
{
$table->increments('id');
$table->integer('user_id')->unsigned(); // This field should be nullable
}
但是,Laravel的模式构建器不支持修改除重命名列之外的列。因此,您将需要运行原始查询来执行,如下所示:
function up()
{
DB::statement('ALTER TABLE `users` MODIFY `user_id` INTEGER UNSIGNED NULL;');
}
为了确保您仍然可以回滚迁移,我们也将执行down()。
function down()
{
DB::statement('ALTER TABLE `users` MODIFY `user_id` INTEGER UNSIGNED;');
}
一个注意事项是,由于您在可空和不可空间之间进行转换,因此您需要确保在迁移之前/之后清理数据。所以在迁移脚本中可以这样做:
function up()
{
DB::statement('ALTER TABLE `users` MODIFY `user_id` INTEGER UNSIGNED NULL;');
DB::statement('UPDATE `users` SET `user_id` = NULL WHERE `user_id` = 0;');
}
function down()
{
DB::statement('UPDATE `users` SET `user_id` = 0 WHERE `user_id` IS NULL;');
DB::statement('ALTER TABLE `users` MODIFY `user_id` INTEGER UNSIGNED NOT NULL;');
}
在OP中有同样的问题 - 同意原始查询解决方案将工作,但根据Laravel 5文档,您应该可以修改为一个列为空:
我有同样的问题。
[Doctrine \ DBAL \ DBALException]请求的
未知列类型“timestamp”。您使用的任何教义类型必须使用\ Doctrine \ DBAL \ Types \ Type :: addType()注册。您可以使用\ Doctrine \ DBA
L \ Types \ Type :: getTypesMap()你能够得到所有支持修改列的类型。如果在数据库内省期间发生此错误,那么您可能忘记为Doctrine类型注册所有数据库类型。使用AbstractPlatform#registerDoctrineTy
peMapping()或让您的自定义类型实现Type#getMappedDatabaseTypes()。如果类型名称为空,则可能是缓存有问题或忘记了一些映射信息。
暂时的类型如下private static $_typesMap = array( self::TARRAY => 'Doctrine\DBAL\Types\ArrayType', self::SIMPLE_ARRAY => 'Doctrine\DBAL\Types\SimpleArrayType', self::JSON_ARRAY => 'Doctrine\DBAL\Types\JsonArrayType', self::OBJECT => 'Doctrine\DBAL\Types\ObjectType', self::BOOLEAN => 'Doctrine\DBAL\Types\BooleanType', self::INTEGER => 'Doctrine\DBAL\Types\IntegerType', self::SMALLINT => 'Doctrine\DBAL\Types\SmallIntType', self::BIGINT => 'Doctrine\DBAL\Types\BigIntType', self::STRING => 'Doctrine\DBAL\Types\StringType', self::TEXT => 'Doctrine\DBAL\Types\TextType', self::DATETIME => 'Doctrine\DBAL\Types\DateTimeType', self::DATETIMETZ => 'Doctrine\DBAL\Types\DateTimeTzType', self::DATE => 'Doctrine\DBAL\Types\DateType', self::TIME => 'Doctrine\DBAL\Types\TimeType', self::DECIMAL => 'Doctrine\DBAL\Types\DecimalType', self::FLOAT => 'Doctrine\DBAL\Types\FloatType', self::BINARY => 'Doctrine\DBAL\Types\BinaryType', self::BLOB => 'Doctrine\DBAL\Types\BlobType', self::GUID => 'Doctrine\DBAL\Types\GuidType', );
@bobbybouwmann:所以你需要运行原始查询来执行,如下所示:
DB :: statement('ALTER TABLEusers
MODIFYuser_id
INTEGER UNSIGNED NULL;');
这似乎不是一个安全的方法。使用表名前缀时,该怎么做?也许有一个函数动态生成表名?ALTER
使用模式构建器还有什么其他方法可以使用表吗?
编辑:
看来,您可以使用DB::getQueryGrammar()->wrapTable($tableName)
来获取原始表名,包括配置的前缀。
现在我的迁移看起来像这样:
<?php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class ChangeBirthdayToTimestampPersonsTable extends Migration{
public function up(){
$tableName = DB::getQueryGrammar()->wrapTable('persons');
DB::statement('ALTER TABLE '.$tableName.' CHANGE `birthday` `birthday` TIMESTAMP NULL;');
}
public function down(){
$tableName = DB::getQueryGrammar()->wrapTable('persons');
DB::statement('ALTER TABLE '.$tableName.' CHANGE `birthday` `birthday` DATE NULL;');
}
}
所以在你的情况下,我会用这个:
function up(){
$tableName = DB::getQueryGrammar()->wrapTable('users');
DB::statement('ALTER TABLE '.$tableName.' MODIFY `user_id` INTEGER UNSIGNED NULL;');
DB::statement('UPDATE '.$tableName.' SET `user_id` = NULL WHERE `user_id` = 0;');
}
function down(){
$tableName = DB::getQueryGrammar()->wrapTable('users');
DB::statement('UPDATE '.$tableName.' SET `user_id` = 0 WHERE `user_id` IS NULL;');
DB::statement('ALTER TABLE '.$tableName.' MODIFY `user_id` INTEGER UNSIGNED NOT NULL;');
}
来自 https://laracasts.com/discuss/channels/general-discussion/l5-migration-issue
PUBLISHED 2 YEARS AGO BY VVINHAS
Hey guys
I'm facing a little bit of a trouble here.
I made a new migration to update a column from not null, to null
php artisan make:migration update_foo_table --table foo
... and in my migration file ...
$table->string('bar')->nullable()->change();
Already did as suggested and required the doctrine/dbal (~2.4)
package under my composer.json
file
The thing is, when I try to migrate, I receive the following error
exception 'Doctrine\DBAL\DBALException' with message 'Unknown column type "varchar(255)" requested. Any Doctrine type that you use has to be registered with \Doctrine\DBAL\Types\Type::addType(). You can get a list of all the known types with \Doctrine\DBAL\Types\Type::getTypesMap(). If this error occurs during database introspection then you might have forgot to register all database types for a Doctrine Type. Use AbstractPlatform#registerDoctrineTypeMapping() or have your custom types implement Type#getMappedDatabaseTypes(). If the type name is empty you might have a problem with the cache or forgot some mapping information.' in /home/vagrant/code/gestec-nit/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:228
What am I doing wrong?
I assume that you're trying to edit a column that you have already added data on, so dropping column and adding again as a nullable column is not possible without losing data. We'll alter the existing column.
Let say you have a migration like this:
Schema::create('users', function(Blueprint $table)
{
$table->increments('id');
$table->integer('user_id')->unsigned(); // This field should be nullable
}
However, Laravel's schema builder does not support modifying columns other than renaming the column. So you will need to run raw queries to do them, like this:
function up()
{
DB::statement('ALTER TABLE `users` MODIFY `user_id` INTEGER UNSIGNED NULL;');
}
And to make sure you can still rollback your migration, we'll do the down() as well.
function down()
{
DB::statement('ALTER TABLE `users` MODIFY `user_id` INTEGER UNSIGNED;');
}
One note is that since you are converting between nullable and not nullable, you'll need to make sure you clean up data before/after your migration. So do that in your migration script both ways:
function up()
{
DB::statement('ALTER TABLE `users` MODIFY `user_id` INTEGER UNSIGNED NULL;');
DB::statement('UPDATE `users` SET `user_id` = NULL WHERE `user_id` = 0;');
}
function down()
{
DB::statement('UPDATE `users` SET `user_id` = 0 WHERE `user_id` IS NULL;');
DB::statement('ALTER TABLE `users` MODIFY `user_id` INTEGER UNSIGNED NOT NULL;');
}
Having the same issue as OP here - agree that the raw query solution will work but according to Laravel 5 docs you should be able to modify to a column to be nullable:
I have the same issue.
[Doctrine\DBAL\DBALException]
Unknown column type "timestamp" requested. Any Doctrine type that you use has to be registered with \Doctrine\DBAL\Types\Type::addType(). You can get a list of all the known types with \Doctrine\DBA
L\Types\Type::getTypesMap(). If this error occurs during database introspection then you might have forgot to register all database types for a Doctrine Type. Use AbstractPlatform#registerDoctrineTy
peMapping() or have your custom types implement Type#getMappedDatabaseTypes(). If the type name is empty you might have a problem with the cache or forgot some mapping information.
@bobbybouwmann: So you will need to run raw queries to do them, like this:
DB::statement('ALTER TABLEusers
MODIFYuser_id
INTEGER UNSIGNED NULL;');
This seems not a safe mothod. How can I do this while using a table name prefix? Maybe there is a function to generate the table name dynamically? Is there any other way to ALTER
the table using the schema builder?
Edit:
It seems that you can use DB::getQueryGrammar()->wrapTable($tableName)
to get the raw table name including the prefix of your configuration.
Now my migration looks like this:
<?php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class ChangeBirthdayToTimestampPersonsTable extends Migration{
public function up(){
$tableName = DB::getQueryGrammar()->wrapTable('persons');
DB::statement('ALTER TABLE '.$tableName.' CHANGE `birthday` `birthday` TIMESTAMP NULL;');
}
public function down(){
$tableName = DB::getQueryGrammar()->wrapTable('persons');
DB::statement('ALTER TABLE '.$tableName.' CHANGE `birthday` `birthday` DATE NULL;');
}
}
So in your case I would use this:
function up(){
$tableName = DB::getQueryGrammar()->wrapTable('users');
DB::statement('ALTER TABLE '.$tableName.' MODIFY `user_id` INTEGER UNSIGNED NULL;');
DB::statement('UPDATE '.$tableName.' SET `user_id` = NULL WHERE `user_id` = 0;');
}
function down(){
$tableName = DB::getQueryGrammar()->wrapTable('users');
DB::statement('UPDATE '.$tableName.' SET `user_id` = 0 WHERE `user_id` IS NULL;');
DB::statement('ALTER TABLE '.$tableName.' MODIFY `user_id` INTEGER UNSIGNED NOT NULL;');
}