欢迎各位兄弟 发布技术文章

这里的技术是共享的

You are here

Unknown column type "timestamp" requested. Any Doctrine type that you use h as 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

[L5] Migration issue

我在这里面临一点麻烦。

我做了一个新的迁移,将列从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

我究竟做错了什么?

bobbybouwmann
 bobbybouwmann
2年前(854,975 XP)

我假设您正在尝试编辑已经添加了数据的列,因此在不丢失数据的情况下,不可能删除列并再次添加为可空列。我们将改变现有的列。

假设你有这样的迁移:

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;');
}
 
gardnersmitha

在OP中有同样的问题 - 同意原始查询解决方案将工作,但根据Laravel 5文档,您应该可以修改为一个列为空:

http://laravel.com/docs/5.0/schema

 
狐狸
狐狸
1年前(1,450 XP)

我有同样的问题。

[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 TABLE usersMODIFY user_idINTEGER 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

 

[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?

bobbybouwmann

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;');
}
 
gardnersmitha

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:

http://laravel.com/docs/5.0/schema

 
狐狸

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 TABLE users MODIFY user_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;');
}

来自 https://laracasts.com/discuss/channels/general-discussion/l5-migration-issue
普通分类: